Pages
 

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!!!!

Pages
 

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" wrote:

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!!!!

Pages
 

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" wrote in message
...
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!!!!

Pages
 

Thank you for responding!


"JLGWhiz" wrote in message
...
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" wrote:

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!!!!

Pages
 

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!



"DanRoss" wrote in message
...
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" wrote in message
...
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!!!!

Pages
 

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

On Sun, 3 Feb 2008 17:42:22 -0800, JLGWhiz
wrote:

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" wrote:

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!!!!

Pages
 

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!



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Sun, 3 Feb 2008 17:42:22 -0800, JLGWhiz

wrote:

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" wrote:

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!!!!