Pages

[OfficeUser]

I need to programatically do something on each page the user sets up to
print. Something to the effect of:
For each page that will be printed
Do this ....
Next

To get the pages that will be printed, it seems I need to determine whether
the user has selected Selection, Active Sheet(s) or Entire Workbook then
determine whether the user has selected All or Page(s). If the user selected
Page(s), then I need to determine which pages.

OR .........

Is there a PagesToBePrinted collection where I can simply use:

For each page in PagesToBePrinted
Do this .....
Next

Can someone help me with how to write the code for the above.

Thanks!!!!

 

[JLGWhiz]

You could probably get the potential page count by counting page breaks after
executing print preview. There might be a way to capture the users printer
settings, but I have never seen it in VBA. There is a limited interface with
printer activity for Excel/VBA.

 

[DanRoss]

I played a little but could not find a good solution to suggest.

You can access some print information and you might play with the WorkBooks
BeforePrint event.

A little more info on what exactly you want to (for each page) might help
clarify a solution.

Dim oPageSetup As Excel.PageSetup
Dim oSheet As Excel.Worksheet
Dim oPage As Excel.Page

For Each oSheet In ThisWorkbook.Worksheets
Set oPageSetup = oSheet.PageSetup

'Print the sheet name and print area for each sheet
Debug.Print oSheet.Name
Debug.Print oPageSetup.PrintArea
Debug.Print "Pages To Print: " & oPageSetup.Pages.Count

'you can also reference the pages but only header/footer info
For Each oPage In oPageSetup.Pages
With oPage
Debug.Print .CenterFooter.Text, .CenterHeader.Text,
..LeftFooter.Text, .LeftHeader.Text, .RightFooter.Text, .RightHeader.Text
End With
Next


Next

 

[OfficeUser]

Thank you for responding!

You could probably get the potential page count by counting page breaks
after
executing print preview. There might be a way to capture the users
printer
settings, but I have never seen it in VBA. There is a limited interface
with
printer activity for Excel/VBA.

 

[OfficeUser]

Thank you for responding!

I want to insert Word Art used like a logo on one or more pages and I want
to be able to select which pages get Word Art inserted.

Thanks!

 

[Gord Dibben]

Dim TotPages As Long
TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
MsgBox "Total Pages is " & TotPages

Se Ron de Bruin's Print Tips site for more on usage of the above.

http://www.rondebruin.nl/print.htm


Gord Dibben MS Excel MVP

 

[OfficeUser]

Thank you for responding!

I want to insert Word Art used like a logo on one or more pages and I want
to be able to select which pages get Word Art inserted. Something like:
For Each Page In Pages
<<Insert word art>>
Next
Any suggestions?

Thanks!