How to get Duplex printing working in Excel?

Question

I'd like to programatically print front and back (Duplex) with Excel. How can I do this, while being agnostic to the type of printer I'm printing to (Assuming it supports duplex printing)

Answer 1

Duplex Printing is a printer option, not an Excel option. Therefore, it can't be directly be set using VBA. Work arounds have been proposed i.e., setting up a duplicate printer with duplexing as default, or using API calls. This is another workaround. A VBA generated multi-tab (page) report must have duplex set on each tab before the report is sent to the printer. Usually, this means manually setting duplex on each tab. However, if the printer with duplex capability is normally available (always in the list of printers when Excel starts), then this workaround should work. The printer doesn’t have to be the default printer and/or can be a network printer. STEPS

1. Create a new TAB and rename it DUPSET
2. Select File, Then Print, and select the printer with duplex capability you will use for printouts
3. Set the print option for the DUPSET tab to flip the page
4. Save the workbook.

When a copy of the DUPSET tab is made, the copy will have the same duplex option set as DUPSET.

Example Macro: (Creates 2 pages and sends them to the printer)

Sub Macro1()
    ‘--- copies DUPSET Tab and positions it as the last Tab ---
    Worksheets("DUPSET").Copy after:=Worksheets(Worksheets.Count)
    ‘--- renames the copied sheet to Page1 --- 
    ActiveSheet.Name = "Page1"
    ‘*** write your data to report “Page1” here ***
    ‘--- copies DUPSET Tab and positions it as the last Tab ---
    Worksheets("DUPSET").Copy after:=Worksheets(Worksheets.Count)
    ‘--- renames the copied sheet to Page2 --- 
    ActiveSheet.Name = "Page2"
    ‘*** write your data to report “Page2” here ***
    ‘--- sends the created pages to printer ---
    Worksheets(Array("Page1”, “Page2").PrintOut
End Sub

Example Macro: (Creates 6 pages and sends them to the printer)

Sub Macro2()
Dim J As Integer, Cnt As Integer, PrnSht As String

Cnt = 6
PrnSht = ""
For J = 1 To Cnt
    ‘--- copies DUPSET Tab and positions it as the last Tab ---
    Worksheets("DUPSET").Copy after:=Worksheets(Worksheets.Count)
    ‘--- renames the copied sheet to Page# --- 
    ActiveSheet.Name = "Page" & Trim(Str(J))
    If J <> Cnt Then
    '--- Generates what's between the brakets ["Page#", ] ---
        PrnSht = PrnSht & Chr(34) & "Page" & Trim(Str(J)) & Chr(34) & "," & Space(1)
    Else
        '--- Generates what's between the brakets ["Page#"] - Last Sheet ---
        PrnSht = PrnSht & Chr(34) & "Page" & Trim(Str(J)) & Chr(34)
    End If        
    ‘*** write your data to report Page# here ***
Next J
‘--- send created pages to printer ---
Worksheets(Array(" & PrnSht & ").PrintOut

End Sub

The DUPSET Tab can be setup as a report page template. The cells can have text or formulas which will be copied to the new sheet. Items like the page margins and print area can also be set and will be duplicated. For a multi-page report, multiple template Tabs could be set up and then copied via VBA. Note: If the printer is not available when Excel starts then a new printer would need to be selected and the option to duplex reset on the DUPSET tab prior to running these macros.

Answer 2

So Duplex Printing isn't a native option to Excel, and it's fairly hard to do.

There are some posts out there about calling the printer API, but those aren't agnostic to what printer you're using.

I got it working by combining SendKeys with a VBS script. Here we go:

Sub SendKeysDuplex()

Application.Dialogs(xlDialogPrinterSetup).Show
ShellCall

End Sub

--

Sub ShellCall()

Shell "wscript C:\FileName.vbs", vbNormalFocus

End Sub

--

Set WSHShell = WScript.CreateObject("WScript.Shell")
WSHshell.AppActivate "Excel.exe"
wsh.sleep 1000 ' wait 1 second
WSHshell.SendKeys "%psp"
WSHshell.SendKeys "{TAB 9}"
WSHshell.SendKeys "{Enter}"
wsh.sleep 3000 ' wait 1 second
WSHshell.SendKeys "{TAB 4}"
WSHshell.SendKeys "n"
WSHshell.SendKeys "y"
WSHshell.SendKeys "{Enter}"
wsh.sleep 500 ' wait 1 second
'WSHshell.SendKeys "{Enter}"
'wsh.sleep 500 ' wait 1 second
'WSHshell.SendKeys "%psp"
'wsh.sleep 1000
WSHshell.SendKeys "{TAB 7}"
WSHshell.SendKeys "{Enter}"
wsh.sleep 500
WSHshell.SendKeys "{Enter}"
wsh.sleep 500

This just prints the tab that's currently active. To have the script just set things then carry on, I recommend integrating it with Wait for shell command to complete

Or simply have one button be a "Set-up Printer" button, and a second button for "Print things"

Also note: This has to be executed via a button on the spreadsheet, finding the macro and hitting F5 won't do it.

Edit: This will only setup the printer on one page. If you need, for example, 14 pages, have the button that launches this macro on the first tab, and have the 14 tabs that you need printed after.

Set WSHShell = WScript.CreateObject( "WScript.Shell" )
WSHshell.AppActivate " Excel.exe " 
wsh.sleep 1000
For i = 1 To 14
WSHshell.SendKeys  "^"&"{PGDN}" 
wsh.sleep 500
WSHshell.SendKeys "%psp" 
wsh.sleep 2500
WSHshell.SendKeys "{TAB 1}" 
wsh.sleep 500
WSHshell.SendKeys "o" 
wsh.sleep 3500
WSHshell.SendKeys "{TAB 4}" 
wsh.sleep 500
WSHshell.SendKeys "n" 
WSHshell.SendKeys "y" 
wsh.sleep 500
WSHshell.SendKeys "{TAB 2}" 
WSHshell.SendKeys "l" 
wsh.sleep 500
WSHshell.SendKeys "{Enter}" 
wsh.sleep 500
WSHshell.SendKeys "{Enter}" 
wsh.sleep 500
Next

Answer 3

Since the duplex option cannot be chosen by VBA directly, you can workaround this by adding the same printer twice to Windows and set the default settings of the second printer to duplex.

• Printer A (standard) <-- your already existing printer
• Printer A (duplex) <-- install the same printer again and set duplex as default

Then you just need to tell Excel to print on the printer named Printer A (duplex). Which will end up in duplex then (because of its default settings).

I used this technique several times and it works good.