Changing printer defaults in Excel Worksheets
 

We have a client that recently moved to Excel 2007. The collate default was
set to unchecked for a period of time in the template and so they have a
number of very active spreadsheets that do not collate properly by default.
We have since
fixed the template and resolved the issue going forward but need a solution
to fix this setting in all of the existing files.

According to the MSFT guys in the Certified Partner forums, I can
programatically fix the collate settings for the files that were created
while the template was incorrect. What objects do I need to call to gain
access to these defaults so that the correct print settings can be set?

Thanks,

J

Changing printer defaults in Excel Worksheets
 

G'day J

I used this code a long time ago, but it should still be relavent in 2007

Change the range & ActivePrinter to suit

ActiveSheet.Select
Range("Z1:AH51").Select
ActiveSheet.PageSetup.PrintArea = "$Z$1:$AH$51"
Application.ActivePrinter = "HP LaserJet 1200 Series PCL 6 on LPT1:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

If you have multiple worksheets that have differing print requirements:

Create a macro for each sheet that requires different print needs and change
the code to suit. e.g:

ActiveSheet.Select
Range("A1:A100").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$A$100"
Application.ActivePrinter = "MyLaserJet on MyNetwork:"
ActiveWindow.SelectedSheets.PrintOut Copies:=10, Collate:=True
or
ActiveSheet.Select
Range("D10:D20").Select
ActiveSheet.PageSetup.PrintArea = "$D$10:$D$20"
Application.ActivePrinter = "MyOtherLaserJet on MyOtherNetwork:"
ActiveWindow.SelectedSheets.PrintOut Copies:=100, Collate:=False

Ask your IT people for the Actual location of your printer(s) on the
network.

HTH
Mark.