comparison between worksheets.count and number of print pages

[cmmadn]

Dear Sirs,

Can anyone tell me if there is a procedure to return the number of print pages (if i workbooks.print all) as an variable, so I can compare the variable againt a worksheets.count (lngWorksheets).

I hope this makes sense and someone can help.

Regards

Andrew

 

[VoG]

Try this

Sub GetPages()
Dim W As Workbook
Dim Pages() As Integer
Dim i As Integer, j As Integer
Set W = ActiveWorkbook
GetPageCounts W, Pages()
For i = LBound(Pages) To UBound(Pages)
    MsgBox W.Worksheets(i).Name & ":  " & Pages(i) & " pages"
    j = j + Pages(i)
Next i
MsgBox "Total pages: " & j
End Sub

Private Sub GetPageCounts(WB As Workbook, PageCounts() As Integer)
Dim SheetName As String
Dim NumSheets As Integer
Dim i As Integer
With WB
    NumSheets = .Worksheets.Count
    ReDim PageCounts(1 To NumSheets) As Integer
    For i = 1 To NumSheets
        SheetName = .Worksheets(i).Name
        PageCounts(i) = ExecuteExcel4Macro("Get.Document(50,""" & SheetName & """)")
    Next i
End With
End Sub

 

[cmmadn]

Thank you, for your reply.

Your code correctly records the number of worksheets in the workbook, which agrees to my records. However, I believe the first secton is trying to count the number of worksheets marked (Page) ... I may be wrong on this!

what I am trying to achieve is to ascertain the number of pages excel expects to print without having to open each workbook individually to record the following

Ctrl + P
Print entire workbook

***No [1] / [7] **** (((for example)))

Then check to ensure the correct formating has occured to ensure the number of prints to be made equals the number of worksheets created, ie 1 print to 1 worksheet contained within the workbook.

I look forward to your reply and thanks again

 

[VoG]

It gives you the printed page count for each sheet in the active workbook, followed by the printed page count for the whole workbook. Tthe workbook must be open for this to work.