How do I choose a network printer in a macro?

[bpeirson]

I have a macro which will print an excel worksheet. I have installed it on another computer in our network but the local printer for that computer is an older model which will not print borders around excel cells or print color.

How can I have this Excel macro choose a specific network printer rather than the default printer. Currently we have to manually change the default printer before running the macro.

An alternate plan is to get the older printer (HP Laser Jet III) to print the borders and use shading in place of colors.

Any help is appreciated.

 

[Melko]

I am not a VBA espert, but I guess that you can execute a external program.

Just execute
rundll32 printui.dll,PrintUIEntry /y /n "printer" /q
for setting the printer you wish as default.

Print

at the end of your macro just repeat again for re set the right one.

 

[bpeirson]

Thanks for the tip Melko. But I stumbled across another solution while I was trying to write in the code you supplied.

Application.ActivePrinter = "[Printer & path]"

It all seems so simple now.

bpeirson.

 

[gb433054]

Using the xlDialogPrinterSetup will display a dialog with all network printers available. The selected printer will then be set as the default.

' Display printer setup dialog
Application.Dialogs.Item(xlDialogPrinterSetup).Show

Hope this helps
Dave

 

[bpeirson]

Thanks, where can I find a list of xlDialogs which are accessible to me in VBA.

Brad

 

[gb433054]

If you open the VBA editor and then open the object browser (From the main mene click View, Object browser). Then type "xldialogs" in the search box, hit enter. This should display all the xldialogs available

hope this helps