Select a printer tray before printing using VBA in Microsoft Excel

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 7 = ten

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>