VBA Sendkeys循环提供打印选项 [英] VBA Sendkeys loop for print options

问题描述

更新:@Blackhawk为我提供了我需要回答的信息-滚动至底部以找到可行的解决方案.

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

我们的系统上安装了一些令人讨厌的新打印软件，这需要我选择Excel工作簿中的每个工作表并分别设置打印选项(即横向，页面大小，整理首选项，颜色等).

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.

支持似乎认为这是完全可以接受的，并没有太大帮助.我发现有两种解决方法，一种是将选定的工作表另存为.pdf，然后打印(只需选择一次打印选项)，另一种是使用sendkeys.从阅读中可以看出，sendkeys不是一个不错的选择，所以如果我必须走.pdf路线，那绝对不理想，因为我的最终用户更喜欢.xls文件(因为这意味着他们可以过滤数据等.)

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.)

因此，我编写了一些VBA，当前该VBA可让我在excel中为一个工作表快速设置打印选项:

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.

但是，我真正想做的是编写VBA以遍历我选择打印的图纸并在每个工作表上运行sendkeys代码.我尝试了以下方法:

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

但是，当观看宏运行时，它会循环浏览每个页面，然后恰好在宏结束时，它似乎试图执行sendkeys部分4次(当然不起作用).

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).

我尝试延迟构建，但是从字面上看，就像代码的application.sendkeys部分仅在宏结束之前才执行.

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.

我正在将Office 2010与Windows 7配合使用，但是任何有关如何使其正常工作的建议(或任何其他想法！)将不胜感激.谢谢

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

最终代码:

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

推荐答案

尝试在Application.Wait之前在其中扔DoEvents.我怀疑Excel需要一些时间来将这些按键作为操作系统事件进行处理.

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.

每个Windows应用程序都有一个消息队列，Windows向该消息队列发送适当的事件消息，以将诸如用户单击或按下键之类的事件通知给它.我的猜测是Excel正在连续运行您的代码(甚至是Wait)，并且没有机会处理按键，因此Windows发送到Excel的消息只是排队.完成宏的运行后，它将处理所有排队的消息并进行处理.

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.

添加 DoEvents 告诉Excel处理其队列中当前所有消息(或消息的某些部分)它们，在这一点上还不太确定)，这意味着运行您的按键并打印工作表.

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.

这篇关于VBA Sendkeys循环提供打印选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！