Printing

[Guest]

I have set up a four page Excel document (A4) to print on both sides of A3.
To print I have set up a macro but each time I opperate the macro I have to
reset:

Properties back to 2 pages per sheet and
Advanced setting for Duplex.

I am on a networked Sharp -AR C150 printer. Can I script it?

Thanks mald

 

[Guest]

yes.
do the whole process on record macro. page setup, print ect
then review code. you will see you have a lot of options.

 

[Dave Peterson]

Some printer settings aren't controllable from VBA.

An alternative is to install a second printer driver, change the settings for
that printer driver, then use that "printer" in your code. You can record
changing printers, printing, and changing printers back.

 

[Guest]

Thanks

I have tried the solution by annon. but this does not pick up the two sided
(Duplex) command. Problem with printer driver solution is that I want to make
this document available on the network will this be ok?

 

[Dave Peterson]

If you go with my suggestion, you'd have to set that printer up on everyone's pc
that would be using the macro. And you could have trouble ensuring that the
names would be identical.

Maybe you could just show a dialog and let the user make the change:

Application.Dialogs(xlDialogPrinterSetup).Show
or
Application.Dialogs(xlDialogPrintPreview).Show

===
I went to google and found these links:

Controlling the Printer from Word VBA
by Jonathan West

Part 1: Using VBA to Select the Paper Tray
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Part 2: Using VBA to control Duplex, Color Mode and Print Quality
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=116

Part 3: Dealing with Different Paper Sizes
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=132

Part 4: Getting printer driver details
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=183


I've never tried them, but you may want to try part 2.

 

[Guest]

Thanks Dave

Mald

If you go with my suggestion, you'd have to set that printer up on everyone's pc
that would be using the macro. And you could have trouble ensuring that the
names would be identical.

Maybe you could just show a dialog and let the user make the change:

Application.Dialogs(xlDialogPrinterSetup).Show
or
Application.Dialogs(xlDialogPrintPreview).Show

===
I went to google and found these links:

Controlling the Printer from Word VBA
by Jonathan West

Part 1: Using VBA to Select the Paper Tray
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Part 2: Using VBA to control Duplex, Color Mode and Print Quality
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=116

Part 3: Dealing with Different Paper Sizes
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=132

Part 4: Getting printer driver details
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=183


I've never tried them, but you may want to try part 2.