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

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.

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