Reading the PrintArea Pages

[Guest]

Before I start on VBA code to do this, I figured I'd ask if anyone has
already has code to do something close or suggestions on how to approach it.
The functions I'm looking for are:

1. NumberOfPrintPages (returns the number of pages setup for printing)
Will this equation do it?
((activesheet.hpagebreaks.count+1) * (activesheet.vpagebreaks.count+1))

2. PrintPageAreas (returns an array containing the Range (or address) of
each print page
This one is a bit more complex. I need to know the range of each page
area.

Thanks,
- Pat

 

[Bill Renaud]

1. You must be sure that PageBreaks have been turned on first, so I would
write your function like the following:

Public Function NumberOfPrintPages(ws As Worksheet) As Long
With ws
.DisplayPageBreaks = True
NumberOfPrintPages = (.HPageBreaks.Count + 1) _
* (.VPageBreaks.Count + 1)
End With
End Function

then call it like so:

Public Sub Test()
Dim lngPages As Long

lngPages = NumberOfPrintPages(ActiveSheet)
End Sub

2. For the range of cells that occupy each printed page, you will have to
iterate through the HPageBreaks and VPageBreaks collections, then get the
Location property of each object that is returned. I don't have code for
this.

 

[JP]

Hello Pat,

To determine the number of pages that will print out, check out this
page:

http://www.j-walk.com/ss/excel/tips/tip65.htm


Hope this helps,
JP

 

[Guest]

Thanks JP.

Amazing, the Excel4 command does exactly what I'm looking for in the page
count.
It tells me the number of pages within my print area. The equation I showed
isn't limited to the print area.

Is there anyplace I can find the definitions of them?

Regards,
- Pat

 

[Bill Renaud]

Pat wrote:
<<Is there any place I can find the definitions of them?>>

ActiveSheet.PageSetup.PrintArea

.... will return a string that represents the print area on the active sheet
(i.e. "$A$1:$P$27").

You would then have to write a routine to count how many page breaks are
inside this area (subset of the whole page), if you want to know how many
printed pages the print area would be.