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 *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.