Recent Addins ADO Application Arrays Charts Commandbars Count Dates Dialogs Events File access Files & Folders Functions Games Help Import & Export Lookup Off topic Other Outlook PivotTables Power Query Printing Problem solving Read me Searching Sorting Sound Strings Sum Time VBA programming VBE Workbooks Worksheets

Select a printer tray before printing

 2000-02-04    Printing    0    221

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.