Excel Doesn't Complete Macro after Opening Another Workbook

18
votes

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)

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.

5
excelvba
You say it doesn't "do anything" - what are you expecting to see? Presumably there's more code following your posted sample. What is FunctionGetFileName ? – Tim Williams
The expected result would be execution of the next line, FunctionGetFileName, and continued execution through the document. FunctionGetFileName is a function I wrote elsewhere in the program which finds the FileName from the FilePath, but there's more code after that which also doesn't execute. From further experimentation, however, I narrowed down the problem and revised the question accordingly. – soytsauce
What version of Excel? Works for me in 2007 with Ctrl+Shift+F. – Tim Williams
I'm using 2007 as well. I don't know why this shouldn't work. Might it have something to do with using global variables? – soytsauce
Do you have any kind of error handling or "on error resume next" ? If yes then comment that out and see what happens. Does the workbook being opened have any auto_open macro ? – Tim Williams

5 Answers

24
votes

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

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

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.

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.

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

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

If you don't want to add the extra code, then your only other options are to not use Ctrl+Shift+Some Letter 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.

4
votes

Just adding a single call to "DoEvents" before calling Workbooks.Open will already do the trick. So the folloiwng snippet will work:

DoEvents
Workbooks.Open Filename:="C:\MyPath\MyFile.xlsx"
1
votes

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.

0
votes

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.

0
votes

Temporary solution found on an French forum: use the below ForEachWinDoEvents before activating the workbook of your choice.

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