Excel VBA print to specific printer tray

Question

In Excel (2007) VBA how do I print the worksheet to a specific printer tray? This will just print to the default tray on the printer:

ws.PrintOut ActivePrinter:="\\print_server\printer_name"

Lymphangitis

Creating different printers seems to be the best. – Lymphangitis

Answer 1

Microsoft offers one ugly solution to this problem; using SendKeys to manually choose the correct tray from the print settings dialog box.

Another option may be to set up additional printers in Windows, where each printer is actually linked to a different tray on the same printer. You could then select the appropriate tray in VBA. This article has the details.

Or, if your printer happens to have a different size of paper in the tray you want to print to, you can set (for example):

ws.PageSetup.PaperSize = xlPaperLegal

And the printer will automatically select the correct tray. I just tried this out on my printer here, and it forced the sheet to print on the manual tray, because I don't actually have a legal size paper tray.

Colosseum

Answer 2

Don't know why or how this works for me but on my system windows xp and on another 10 if you have multiple worksheets in excel, go to file,print,page setup then set to print on tray 2 etc. somehow it remembers that for the next time.

Orectic

I understand that some products may look like or even be lumpy garbage. However, why not take some time and explore this feature a little deeper before sharing knowledge that, in its current state, is a superficial hack? – Extravagant