VBA Replace Sendkeys to select option in print settings

Question

When printing in Excel, my workplace has an additional pop up window for selecting print options. It is not part of Excel (I believe it is a canon printer dialogue window). These options allow you to specify to print in colour, staple and collate etc. They are not excel print options.

In the past, I have used a macro which uses SendKeys to replicate the keyboard shortcuts used to select (in Excel) Page Layout (alt P), Page Setup (alt I), and then 'Options' in the Page Setup screen (alt O). Once selecting 'Options', the printer dialogue screen opens and the macro continued to use SendKeys to select the profile in this window (each profile contains options to print in colour, staple and collate etc). The piece of code is as follows:

Sub Test()

    Application.SendKeys ("%p"), True 'Selects Page Layout
    Application.SendKeys ("%i"), True 'Selects Print Titles
    Application.SendKeys ("%o"), True 'Selects Options
    Application.SendKeys ("p"), True  'Selects 'Portrait' default (this needs to be set up initially)
    Application.SendKeys "{TAB 19}", True 'Tabs to OK
    Application.Wait (Now() + TimeValue("00:00:01"))
    Application.SendKeys "~", True 'Hits enter to close screen
    Application.Wait (Now() + TimeValue("00:00:01"))
    Application.SendKeys "~", True 'Hits enter to close screen

End Sub

Since moving to Windows 10/Office 2016 - the SendKeys fails at the point where the separate printer window opens (specifically at the line starting with Application.SendKeys ("p"), True and beyond). Basically, the macro will open up the printer settings window but do nothing after that.

I have tried looking for a replacement to SendKeys, but I am struggling to understand how I can - via VBA - automate the process to hit p (selects portrait profile in print dialogue window), hit tab 19 times (to get to the exit screen button), and hit enter twice (to close subsequent dialogue windows - which are excel windows). To be clear - the 'portrait' profile mentioned is a specific printer profile which specifies a number of options including orientation, 2 sided printing, binding location, color mode and the staple/collate/group preference.

I would be quite happy to replace all of the SendKeys commands if possible as I understand they are not reliable/supported.

[Update 14.05.2019]:

So I've looked into trying to replace the sendkeys with 'Keybd_Event' instead, but this hits exactly the same roadblock (works right until the printer dialogue window opens).

[Update 20.05.2019]

@Selkie's solution worked, and I have marked it as the answer.

This was the code that I used in the end, although still need to tweak it so that it loops through selected sheets:

Sub PrinterSetUp()
Dim filepath As String
Dim Msg As Integer

'Filepath can't have a space in it
filepath =         "Directory\PrinterScriptPortrait.vbs"

If Dir(filepath) <> "" Then
'Hurray it exists
Else
'It doesn't exist yet, create the file
WriteVBSScript (filepath)
End If

Shell "wscript " & filepath, vbNormalFocus
'no code after here, otherwise everything breaks.
End Sub


Sub WriteVBSScript(filepath As String)
Dim VBScriptString As String
Dim fso As Object
Dim oFile As Object

'Needs to be done as a VBS script because VBA and sendkeys don't play well with dialog boxes.
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFile = fso.CreateTextFile(filepath)


VBScriptString = VBScriptString & "Set WSHShell = WScript.CreateObject( " & Chr(34) & "WScript.Shell" & Chr(34) & " )" & vbNewLine
VBScriptString = VBScriptString & "WSHshell.AppActivate " & Chr(34) & " Excel.exe " & Chr(34) & " " & vbNewLine
VBScriptString = VBScriptString & "wsh.sleep 1000" & vbNewLine
VBScriptString = VBScriptString & "WSHshell.SendKeys " & Chr(34) & "%p" & Chr(34) & " " & vbNewLine
VBScriptString = VBScriptString & "wsh.sleep 1000" & vbNewLine
VBScriptString = VBScriptString & "WSHshell.SendKeys " & Chr(34) & "%i" & Chr(34) & " " & vbNewLine
VBScriptString = VBScriptString & "wsh.sleep 1000" & vbNewLine
VBScriptString = VBScriptString & "WSHshell.SendKeys " & Chr(34) & "%o" & Chr(34) & " " & vbNewLine
VBScriptString = VBScriptString & "wsh.sleep 1000" & vbNewLine
VBScriptString = VBScriptString & "WSHshell.SendKeys " & Chr(34) & "p" & Chr(34) & " " & vbNewLine
VBScriptString = VBScriptString & "wsh.sleep 1000" & vbNewLine
VBScriptString = VBScriptString & "WSHshell.SendKeys " & Chr(34) & "{TAB 19}" & Chr(34) & " " & vbNewLine
VBScriptString = VBScriptString & "wsh.sleep 1000" & vbNewLine
VBScriptString = VBScriptString & "WSHshell.SendKeys " & Chr(34) & "~" & Chr(34) & " " & vbNewLine
VBScriptString = VBScriptString & "wsh.sleep 1000" & vbNewLine
VBScriptString = VBScriptString & "WSHshell.SendKeys " & Chr(34) & "~" & Chr(34) & " " & vbNewLine
VBScriptString = VBScriptString & "wsh.sleep 1000" & vbNewLine

oFile.WriteLine VBScriptString

oFile.Close

Set fso = Nothing
Set oFile = Nothing

End Sub

Its unclear how do you trigger the print : with VBA code or manually pressing standard excel buttons? if VBA show the code. — Thomas G, May 13 '19 at 12:10
The print is triggered by VBA - the sendkeys statement above i.e 'select options' is how the printer dialogue window is opened - the sendkeys literally replicate the keyboard shortcut commands you would use. I will amend my question. — bawpie, May 13 '19 at 12:32
What happens if you trigger the printing using something like `ActiveSheet.PrintOut` ? do you see the canon dialog box ? — Thomas G, May 13 '19 at 12:39
@ThomasG - nothing happen, no message just a brief pause but that's it. It doesn't send anything to the print queue. — bawpie, May 13 '19 at 12:44
Mmh you might have no proper default or active printer defined, try like this : `ActiveSheet.PrintOut copies:=1, ActivePrinter:="name of your printer"` where name of your printer is a valid name of course. You can find the poper name in "Devices and Printers" — Thomas G, May 13 '19 at 13:12
@ThomasG - sorry, realised I was trying to print a blank workbook, once there was something to print I got the 'now printing' dialogue and sends it to the printer, but it doesn't open the options window. — bawpie, May 13 '19 at 13:25
ok, but that's ultimately what you want:sending it to the printer directly, with no dialog box. In theory, you have the ability to define all your printing parameters in the PrintOut command — Thomas G, May 13 '19 at 13:32
@ThomasG - thanks, but I need to set staple position and colour printing and I'm not sure either of those can be done via PrintOut or even in VBA. For example, '.PageSetup.BlackAndWhite = False' doesn't result in the file being printed out in colour. — bawpie, May 13 '19 at 13:53
this might be helpful http://www.lessanvaezi.com/changing-printer-settings-using-the-windows-api/ — cyboashu, May 13 '19 at 18:52
Have you tried locating the other Window using the Windows API and searching by name, as in this answer: https://stackoverflow.com/questions/25098263/how-to-use-findwindow-to-find-a-visible-or-invisible-window-with-a-partial-name — Greg Viers, May 13 '19 at 19:00
@GregViers - not sure how to use this, as I can only run the macro when the printer dialogue window is closed, when it's open I can't switch back to excel to run it. — bawpie, May 14 '19 at 07:31
Are you open to using other languages? It might be a little different, but you could automate the form being filled out much easier with a language called Autohotkey. — Ryan Wildry, May 14 '19 at 14:35
@RyanWildry - I will check this out thanks, we are restricted as to what we can install due to IT policies, but seeing as it's free and open source I'll see if they would consider it. — bawpie, May 14 '19 at 14:53
You could make a macro that triggers the print dialog window, then includes this portion. Then you add a print button on the worksheet. — Greg Viers, May 15 '19 at 13:08
@GregViers - Thanks, tried that but whether I call the 'Test' macro or include it directly in a macro with the printer dialogue trigger, it fails to trigger the printer dialogue and just gives me the window handle almost like it's being run prior to the commands to open the dialogue window are run. — bawpie, May 17 '19 at 07:14

Selkie · Accepted Answer · 2019-05-14T21:29:42.323

So this is really tricky, and I only recently managed to figure it out.

Basically, when you open up a window like that, sendkeys stops working - from Excel.

The solution? Invoke the sendkeys externally.

Here's a sample code that I wrote to change the printer type to Duplex printing on the 15 sheets after the sheet where the button is located:

Sub PrinterSetUp()
Dim filepath As String
Dim Msg As Integer

'This code is super janky, I apologize.

'Filepath can't have a space in it
filepath = "Filepath\PrinterScript.vbs"
 'Select the printer we want to print to
Application.Dialogs(xlDialogPrinterSetup).Show


If Dir(filepath) <> "" Then
    'Hurray it exists
Else
    'It doesn't exist yet, create the file
    WriteVBSScript (filepath)
End If

Shell "wscript " & filepath, vbNormalFocus
'no code after here, otherwise everything breaks.
End Sub





Sub WriteVBSScript(filepath As String)
Dim VBScriptString As String
Dim fso As Object
Dim oFile As Object

'Needs to be done as a VBS script because VBA and sendkeys don't play well with dialog boxes.
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFile = fso.CreateTextFile(filepath)


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

oFile.WriteLine VBScriptString

oFile.Close


Set fso = Nothing
Set oFile = Nothing




End Sub

How it works: It checks that there's an external VBS script. If it doesn't find it, it'll write it, then it'll use powershell to invoke the script. The script will then use sendkeys "externally" from Excel to make everything work - overriding Excel's innate "Can't use sendkeys for dialogs"

I had to lower my standards pretty far down before being able to do this, and I don't recommend it. I believe for most everything besides duplex printing, there are other options. However, this does make sendkeys work with Excel dialogs, which is what you were asking about.

You will of course need to edit the code to work for what you're trying to do with the sendkeys - an easier way would be to simply write the VBS script directly - I needed my script to work on whatever computer and whatever filepath directory I happened to be on, hence the write-then-use features.

Your script translated would look something like:

VBScriptString  = VBScriptString  & "    Application.SendKeys (" & Chr(34)& "%p" & Chr(34)& "), True 'Selects Page Layout" & vbnewline
VBScriptString  = VBScriptString  & "    Application.SendKeys (" & Chr(34)& "%i" & Chr(34)& "), True 'Selects Print Titles" & vbnewline
VBScriptString  = VBScriptString  & "    Application.SendKeys (" & Chr(34)& "%o" & Chr(34)& "), True 'Selects Options" & vbnewline
VBScriptString  = VBScriptString  & "    Application.SendKeys (" & Chr(34)& "p" & Chr(34)& "), True  'Selects 'Portrait' default (this needs to be set up initially)" & vbnewline
VBScriptString  = VBScriptString  & "    Application.SendKeys " & Chr(34)& "{TAB 19}" & Chr(34)& ", True 'Tabs to OK" & vbnewline
VBScriptString  = VBScriptString  & "    Application.Wait (Now() + TimeValue(" & Chr(34)& "00:00:01" & Chr(34) & "))" & vbnewline
VBScriptString  = VBScriptString  & "    Application.SendKeys " & Chr34 & "~" & Chr(34)& ", True 'Hits enter to close screen" & vbnewline
VBScriptString  = VBScriptString  & "    Application.Wait (Now() + TimeValue(" & Chr(34)& "00:00:01" & Chr(34)& "))" & vbnewline
VBScriptString  = VBScriptString  & "    Application.SendKeys " & Chr(34)& "~" & Chr(34)& ", True 'Hits enter to close screen" & vbnewline

Of course, you might want to use the default method:

Worksheets("Sheet1").PageSetup.Orientation = xlPortrait

This is a much easier way to make a sheet print in portrait mode

Thanks for this, although I'm not trying to set portrait in Excel, portrait is the name of the profile in the printer dialogue window which sets things like colour printing, staple position etc. I'm not at work at the moment but will give this a go although I've started work on an alternative solution which might prove more future proof! — bawpie, May 16 '19 at 09:02
@bawpie: Ah, got it. I saw the portrait part of the code, and thought you were trying to set that setting. I'll agree sendkeys isn't very future proof, but the one upside to this is that it's pure Excel/VBA :) — Selkie, May 16 '19 at 15:37
so I've given this a go this morning, and it creates the script in the specified path but I then get a message stating 'Application Access Denied' which I guess is occurring at the 'Shell "wscript " & filepath,vbNormalFocus' line. I've tried hardcoding the line too but get the same error message - it may be because I'm doing it via remote access so I'll give it a go once I get back in the office. Thanks again! — bawpie, May 17 '19 at 07:05
Yeah that’s probably due to your systems settings - that’s not a vba error as far as I know — Selkie, May 17 '19 at 15:18
the error was Citrix related, following your advice I've adjusted your existing code and it works. One snag though - I need this to loop through selected sheets in the workbook. I think this was being done in your original code, so I will have a further play round. I am going to mark this as the solution though, as it is doing exactly as I asked in my original question - really appreciate the help! — bawpie, May 20 '19 at 10:50
Yup, you need to loop it, and I've looped in my original code - the sheets do need to be in order — Selkie, May 20 '19 at 15:29

VBA Replace Sendkeys to select option in print settings

1 Answers1