Defining which printer to be used

[mikeymay]

Is it possible to allow a user to select the printer on which they want somethign priting to using code?

 

[Andrew Poulsom]

Like this?

Application.Dialogs(xlDialogPrinterSetup).Show

 

[JugglerJAF]

To show the print dialog box use:

Application.Dialogs(xlDialogPrint).Show

If you know the specific printer that should be used, you can change the active printer to that one, print the document and then change the printer back to default by using:

'set printer to 4015
Application.ActivePrinter = "\\network\LASERJET_4015 on Ne09:"

'print document
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'reset printer to PCL5
Application.ActivePrinter = "\\network\LaserJet_4250 on Ne07:"

Just change the first and last lines to your actual printers (record a macro where you print the document if you don't know the network paths).

 

[mikeymay]

Is it possible to inform the user if the printer they have selected has colour print capability?

 

[Andrew Poulsom]

Maybe this will help you:

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=116

 

[mikeymay]

Thanks Andrew but this code seems to relate to Word VBA and not sure what I need to change other than the un recognised expression, but then I don't know what to change to....

 

[mikeymay]

I was wondering if using

Application.Dialogs(xlDialogPrinterSetup).Show

would allow me to define the printer as an object and then access its properties?

 

[Andrew Poulsom]

Thanks Andrew but this code seems to relate to Word VBA and not sure what I need to change other than the un recognised expression, but then I don't know what to change to....

The code uses the Windows API, so it's not applicable only to Word.

 

[ronmcnaughton]

in the code example; Application.ActivePrinter = \\network\LASERJET_4015 on Ne09:

I am working with a button to allow printing of a workbook. Everything has been going along fine for the past several months. This morning it would not work and told me I had an invalid printer.
After re-recording the macro I found that the printer had changed from Ne04 to Ne05.
Is there anyway to determine this value prior to printing so that I can set the parameter correctly in the macro without having to re-record?

Thanks.

 

[Andrew Poulsom]

This may help you:

http://www.dailydoseofexcel.com/archives/2008/04/05/getting-the-printer-port/