VBA Print Code for Generically Selecting a Printer 1

[bstanton]

I have the following code that I recorded with the Macro recorder in Excel to print a user selection to a USB printer.

Application.ActivePrinter = "HP LaserJet P2035 UPD PCL 5 on Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"HP LaserJet P2035 UPD PCL 5 on Ne01:", Collate:=True

The code works as intended, but I am wondering if there is a way I can rewrite this code so its not port dependent. The way the code is currently written, it needs to know which USB port the printer is plugged into (I believe this is controlled by the Ne01 in the code), but I plan to install this code on numerous computers and inevitably the printer will not always be plugged into the same USB port. I would like to have the code find the printer no matter what USB port it is plugged into and print to it. I hope that someone has some ideas of how to write this code in variable form to achieve this. Thank you in advance for your help.

 

[soccercook44]

Will the printer being printed to always be the default? If so you should be able to do something like this:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

 

[bstanton]

Thanks, I thought about that, but unfortunately it will not be the default printer. If you have any other ideas please let me know?

 

[TonyJollans]

See

http://word.mvps.org/FAQs/MacrosVBA/AvailablePrinters.htm

for some code to get a list of available printers. If you can identify your printer from the list (potentially a complex decision as you are working just with text), you're home and dry.

If you need more detail, there is a series of articles by former MVP Jonathan West, about controlling printers from VBA, starting here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101,

and also a page by MVP Graham Mayor that covers some similar ground, here:

http://www.gmayor.com/fax_from_word.htm

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

Tony,

Good info! Thanks for the valuable post!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]