打开另一个工作簿后，Excel无法完成宏

问题描述

我正在尝试让VBA触发命令

I'm trying to get VBA to fire the commands

sImportFilePath = Application.GetOpenFilename(FileFilter:= _
"Excel Files (*.xls), *.xls", Title:="Choose The Source File")
Application.Workbooks.Open (sImportFilePath)
sImportFileName = FunctionGetFileName(sImportFilePath)

当我逐步执行该功能时，它们会起作用，但是当我使用热键 Ctrl + Shift + F 或任何其他热键时，Application.Workbooks.Open命令会起作用，但会浏览新的Excel文档，则什么也不做.但是，当我在开发人员标签中打开宏"时，选择我的宏，然后单击运行"，一切运行正常.

And they work when I step through the function, but when I use the hotkey Ctrl+Shift+F or any other hotkey, the Application.Workbooks.Open command 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.

推荐答案

我本人实际上遇到了这个确切的问题，并最终找到了解决问题的方法.

I actually ran into this exact problem myself and finally found a solution to my problem.

这是您用来调用代码的键盘快捷键中的按钮.

It is the button in the keyboard shortcut you are using to call your code.

显然，Excel中有一个专门设计用来阻止在打开工作簿并按下 Shift 键时运行代码的功能，但不幸的是，这也影响通过VBA使用Workbook.Open方法打开工作簿.在知识库文章555263 中提到了这一点，适用于Excel 2000& 2003年，但是我在Excel 2010中遇到了相同的问题，因此我想它也会影响2007年.

Apparently there a feature in Excel specifically designed to prevent code from running when a workbook is opened and the Shift key is pressed, but unfortunately this also impacts opening workbooks with the Workbook.Open method 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.

特别是当您尝试在程序的早期通过代码打开工作簿时，才会发生这种情况.如果在您没有足够的时间实际放开 Shift 按钮之前已在代码中到达Workbook.Open调用，则Excel会将其解释为试图阻止代码运行并中止该过程.而且没有错误消息或我发现的任何东西.它只是突然停止.

This occurs specifically when you try to open a workbook via code very early in the program. If the Workbook.Open call is reached in code before you have had sufficient time to actually let go of the Shift button, 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键被释放.

The workaround/fix is to force the code to wait for the Shift key to be released before issuing the Workbook.Open command.

根据文章，只需将此代码添加到您的宏中即可，

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

(注意:此代码适用于32位版本的Excel.64位版本将需要使用Declare语句上的PtrSafe属性.)

(NOTE: This code is for 32-bit versions of Excel. 64-bit versions will need to use the PtrSafe attribute on the Declare statement).

如果您不想添加额外的代码，那么您唯一的其他选择是不使用 + + ++ to launch a macro, or to put the Workbook.Open command later in the macro (not right at the beginning) in order to give yourself time to release the Shift button after starting it.

这篇关于打开另一个工作簿后，Excel无法完成宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！