VBA Sendkeys loop for print options

Question

Update: @Blackhawk provided me with the piece of information I needed to answer the question - scroll to the bottom for a working solution.

We've had some annoying new print software installed on our systems which requires me to select each worksheet in an Excel workbook and set the print options (i.e. landscape, page size, collate preference, colour etc) on it separately.

Support seems to think this is perfectly acceptable, and aren't much help. There are two work arounds that I've found, one is to save the selected sheets as a .pdf and then print (only needing to select print options once) and the other is to use sendkeys. From reading, I can see that sendkeys are not a good choice, so if I have to go the .pdf route I will, but this is definitely not ideal, as my end users prefer .xls files (because it means they can filter data etc.)

Because of that, I've written some VBA that will currently allow me to quickly set print options for one worksheet in excel:

Sub A00_Sendkeystest()
'

Application.Wait (Now() + TimeValue("00:00:01"))

Application.SendKeys ("%p"), True 'Selects Page Layout
Application.SendKeys ("%i"), True 'Selects Print Titles
Application.SendKeys ("%o"), True 'Selects Options
Application.SendKeys ("%f"), True 'Selects profile
Application.SendKeys ("l"), True  'Selects 'Landscape' default (this needs to be set up initially)
Application.SendKeys "{TAB 14}", True 'Tabs to OK
Application.SendKeys "~", True 'Hits enter to close screen
Application.SendKeys "{TAB 11}", True 'Tabs to OK
Application.SendKeys "~", True 'Hits enter to close screen

End Sub

This runs quite nicely when assigned to a shortcut, so I can run it quite quickly against one sheet in a workbook.

However, what I'd really like to do is write the VBA to loop through the sheets I've selected for printing and run the sendkeys code against each worksheet. I've tried the following:

Sub cycle()

    Dim ws As Worksheet
    For Each ws In ActiveWindow.SelectedSheets

    ws.Activate

    'Application.Wait (Now() + TimeValue("00:00:10"))

    Application.SendKeys ("%p") ', True 'Selects Page Layout
    Application.SendKeys ("%i") ', True 'Selects Print Titles
    Application.SendKeys ("%o") ', True 'Selects Options
    Application.SendKeys ("%f") ', True 'Selects profile
    Application.SendKeys ("l") ', True  'Selects 'Landscape' default (this needs to be set up initially)
    Application.SendKeys "{TAB 14}" ', True 'Tabs to OK
    Application.SendKeys "~" ', True 'Hits enter to close screen
    Application.SendKeys "{TAB 11}" ', True 'Tabs to OK
    Application.SendKeys "~" ', True 'Hits enter to close screen

    Application.Wait (Now() + TimeValue("00:00:03"))

    Next

End Sub

However, when watching the macro run, it cycles through each page and then just as the macro ends, it appears to try to execute the sendkeys section 4 times (which doesn't work of course).

I've tried building in a delay but it's literally like the application.sendkeys part of the code only executes just before the macro ends.

I'm using Office 2010 with Windows 7, but any suggestions on how to get this to work (or any alternative ideas!) would be much appreciated. Thanks

Final code:

Sub cycle()

'Macro will cycle through selected sheets and select landscape_printing profile in print options.

    Application.ScreenUpdating = False

    Dim ws As Worksheet
    For Each ws In ActiveWindow.SelectedSheets

    ws.Activate

    Application.SendKeys ("%p"), True 'Selects Page Layout
    Application.SendKeys ("%i"), True 'Selects Print Titles
    Application.SendKeys ("%o"), True 'Selects Options
    Application.SendKeys ("%f"), True 'Selects profile
    Application.SendKeys ("l"), True  'Selects 'Landscape' default (this needs to be set up initially)
    Application.SendKeys "{TAB 14}", 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

    DoEvents

    Next

    Application.ScreenUpdating = True

    MsgBox "Completed."

End Sub

Answer 1

Try throwing an DoEvents in there, right before the Application.Wait. I suspect that Excel needs a moment to process those keypresses as OS events.

Every windows application has a message queue, to which Windows sends appropriate event messages to notify it of things like the user clicking or pressing keys. My guess is that Excel is continuously running your code (even the Wait) and doesn't have a chance to process the keypresses, so the messages that Windows sends to Excel just queue up. When it finishes running the macro, it turns to handling all the queued up messages and handles them.

Adding a DoEvents tells Excel to process any messages currently in its queue (or some portion of them, not quite sure on that point), which means running your keypresses and printing the sheet.