vba 打开另一个工作簿后，Excel 无法完成宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6959005/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Doesn't Complete Macro after Opening Another Workbook
提问by soytsauce
I'm trying to get VBA to fire the commands
我正在尝试让 VBA 触发命令
sImportFilePath = Application.GetOpenFilename(FileFilter:= _
"Excel Files (*.xls), *.xls", Title:="Choose The Source File")
Application.Workbooks.Open (sImportFilePath)
sImportFileName = FunctionGetFileName(sImportFilePath)
And they work when I step through the function, but when I use the hotkey Ctrl+Shift+For any other hotkey, the Application.Workbooks.Opencommand works but it navigates the the new Excel document, then doesn't do anything. However, when I open "Macros" in the developer tab, select my macro, and click "Run" everything is runs fine.
当我逐步执行该功能时，它们会起作用，但是当我使用热键Ctrl+Shift+F或任何其他热键时，该Application.Workbooks.Open命令会起作用，但它会导航新的 Excel 文档，然后什么也不做。但是，当我在开发人员选项卡中打开“宏”时，选择我的宏，然后单击“运行”，一切正常。
回答by psubsee2003
I actually ran into this exact problem myself and finally found a solution to my problem.
我实际上自己遇到了这个确切的问题，并最终找到了解决我的问题的方法。
It is the Shiftbutton in the keyboard shortcut you are using to call your code.
它是Shift您用来调用代码的键盘快捷键中的按钮。
Apparently there a feature in Excel specifically designed to prevent code from running when a workbook is opened and the Shiftkey is pressed, but unfortunately this also impacts opening workbooks with the Workbook.Openmethod via VBA. This was mentioned in KB Article 555263, applying to Excel 2000 & 2003, but I encountered the same problem in Excel 2010, so I imagine it is also impacting 2007 as well.
显然，Excel 中有一项功能专门用于在打开工作簿并按下Shift键时防止代码运行，但不幸的是，这也会影响Workbook.Open通过 VBA使用该方法打开工作簿。这在KB 文章 555263中提到，适用于 Excel 2000 和 2003，但我在 Excel 2010 中遇到了同样的问题，所以我想它也会影响 2007。
This occurs specifically when you try to open a workbook via code very early in the program. If the Workbook.Opencall is reached in code before you have had sufficient time to actually let go of the Shiftbutton, Excel is interpreting that as an attempt to block code from running and aborts the process. And there are no error messages or anything that I have found. It just stops abruptly.
当您在程序的早期尝试通过代码打开工作簿时，尤其会发生这种情况。如果Workbook.Open在您有足够的时间实际松开Shift按钮之前在代码中调用了该调用，Excel 会将其解释为试图阻止代码运行并中止该过程。并且没有错误消息或我发现的任何内容。它只是突然停止。
The workaround/fix is to force the code to wait for the Shift key to be released before issuing the Workbook.Opencommand.
解决方法/修复是强制代码在发出Workbook.Open命令之前等待 Shift 键被释放。
Per the article, just add this code to your macro and that should do it:
根据文章，只需将此代码添加到您的宏中即可：
'Declare API
Declare Function GetKeyState Lib "User32" (ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16
Function ShiftPressed() As Boolean
'Returns True if shift key is pressed
ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function
Sub Demo()
Do While ShiftPressed()
DoEvents
Loop
Workbooks.Open Filename:="C:\MyPath\MyFile.xlsx"
End Sub
(NOTE: This code is for 32-bit versions of Excel. 64-bit versions will need to use the PtrSafeattribute on the Declarestatement).
（注意：此代码适用于 32 位版本的 Excel。64 位版本将需要使用语句PtrSafe上的属性Declare）。
If you don't want to add the extra code, then your only other options are to not use Ctrl+Shift+Some Letterto launch a macro, or to put the Workbook.Opencommand later in the macro (not right at the beginning) in order to give yourself time to release the Shift button after starting it.
如果你不想增加额外的代码，那么你唯一的其他选择是不使用Ctrl+ Shift+Some Letter启动一个宏，或把Workbook.Open以给自己时间后，命令在宏（不对开头）启动后松开Shift键。
回答by Peter Heijnen
Just adding a single call to "DoEvents" before calling Workbooks.Open will already do the trick. So the folloiwng snippet will work:
只需在调用 Workbooks.Open 之前添加对“DoEvents”的单个调用就可以解决问题。因此，以下代码段将起作用：
DoEvents
Workbooks.Open Filename:="C:\MyPath\MyFile.xlsx"
回答by Joe Nagy
Question did you have a line of code selecting multiple tabs anytime before this line of code? I have found that this is sort of like holding in the shift key the entire time they're selected. To resolve this I had the macro ungroup (single select) a tab and the macro began working then.
问题您是否有一行代码在这行代码之前随时选择多个选项卡？我发现这有点像在选择它们的整个过程中都按住 shift 键。为了解决这个问题，我让宏取消组合（单选）一个选项卡，然后宏开始工作。
回答by vkc34
Temporary solution found on an French forum: use the below ForEachWinDoEventsbefore activating the workbook of your choice.
在法语论坛上找到的临时解决方案： 在激活您选择的工作簿之前使用下面的ForEachWinDoEvents。
Sub Test()
Application.ScreenUpdating = False
Set w1 = Workbooks.Add(xlWBATWorksheet)
Set w2 = Workbooks.Add(xlWBATWorksheet)
Set w3 = Workbooks.Add(xlWBATWorksheet)
Application.ScreenUpdating = True
ForEachWinDoEvents
w2.Activate
End Sub
Sub ForEachWinDoEvents()
Dim win As Window
For Each win In Application.Windows
DoEvents
Next win
End Sub
回答by Mind Driver
A simple workaround that did it for me is to assign the VBA to a shortcut key without a shift. I'm not a big fan of doing so because there are many more conflicts with dafault Excel shortcuts. But there's a few available as of Excel 2010 based on this article: Ctrl+e, Ctrl+j, Ctrl+m, Ctrl+q.
为我做的一个简单的解决方法是将 VBA 分配给一个没有移位的快捷键。我不太喜欢这样做，因为与 dafault Excel 快捷方式存在更多冲突。但是根据这篇文章，从 Excel 2010 开始，有一些可用：Ctrl+e、Ctrl+j、Ctrl+m、Ctrl+q。
