Select a printer tray before printing

When you print documents from Word with VBA it's possible to select which printer tray the document is supposed to get a sheet from. In Excel you don't have the opportunity to set the properties FirstPageTray or OtherPagesTray like you can in Word. It's possible to create a simple solution by using SendKeys. Here are two example macros:

Sub ChangeTray()
' selects the manual page tray without printing
    Application.SendKeys "%fu%e{TAB}{DOWN}{DOWN}{TAB}m~{ESC}", True
End Sub

Sub ChangeTrayAndPrint()
' selects the manual page tray and prints the selected sheet(s)
    Application.SendKeys "%fu%e{TAB}{DOWN}{DOWN}{TAB}m~~", True
End Sub

The example macros above will work with the norwegian version of Excel97 together with a Canon bubblejet printer. The content of the textstring that you transfer with SendKeys may be different from computer to computer depending on the version of Excel and the installed printer. You will have to edit the above macros to make them work for you on your computer. You will find a complete list of special characters that replaces the special function keys in the built-in help for VBA, type SendKeys in a module, select the word and press the F1-key.

Document last updated 2000-02-04 12:50:09

User comments:

Ole P. from Norway wrote (2004-09-13 21:49:21 CET):

Re: Printer tray selection MSword2k using Macros
Hi!

Word documents have more settings available than Excel worksheets, and you are able to define which paper trays that are supposed to be used when printing the document. You should not have to use the SendKeys method described above in this document.
Try to record a new macro in Word while you are changing/setting the proper page tray settings for your document. You will get code that look something like this when you edit out code that is not relevant:

Sub ChangeWordDocumentTray()
With ActiveDocument.PageSetup
.FirstPageTray = wdPrinterUpperBin
.OtherPagesTray = wdPrinterDefaultBin
End With
End Sub

This is simple enough, the only downside to this method is that the values/settings for the different paper trays often are different from printer to printer...

Dave Ryan from Dublin,Ireland wrote (2004-09-13 19:49:42 CET):

Printer tray selection MSword2k using Macros
Hi,I would be very grateful if you could help with my nightmare. I have a hp2300 printer and users are used to having a macro on the tool bar,one for plain paper and another for printed paper which selects the proper trays in the printer.

Since I upgraded the computers and saved the normal.dot back into word the Macros do not work. The computers are using XP and 2K. It worked fine under Win98.

Could you please help before I get the sack!! I looked into this but havent been able to figure it out creating new Macros. Any chance of a lay man's explanation or whatever help you can give.

Many thanks,

D

Ole P. from Norway wrote (2003-08-28 22:35:47 CET):

RE: Printer Options
You can find an example here:
http://www.mvps.org/word/FAQs/MacrosVBA/AvailablePrinters.htm

Holly from United States wrote (2003-08-28 18:31:51 CET):

Printer Options
Hi,
I am creating a macro in MS Word to do mailmerge and printing automation. After mailmerged, the merged document should be send to the printer. In the process of sending to printer, I like the users be able to select of what printer they want the document to be printed. Please help me on the syntax of printer options. Thanks.