The problem described below has been solved. The VBA macro described below is called via a CTRL-SHIFT-P. But when the shift key is held down like this in calling any macro which has a Workbooks.Open command, the Workbooks.Open command is killed, and execution of the macro is stopped. The infuriating thing is that Microsoft has known about this problem since about the year 2000, and, apparently hasn't come up with a fix yet--All versions from Microsoft Excel 2000 up to the present are affected. (See Knowledge Base article ID 555263, last reviewed by Microsoft on 2/26/05.) The workaround: Do not use the shift key in any hotkey used to call any Excel macro which contains a Workbooks.Open command. This problem cost me about 6 hours of hard work, research, testing, striving for a workaround. Where do I send the bill?
Greetings! This problem has me really stumped. While running Macro Scheduler macros, I ran a VBA macro in Excel 2007. My operating system is Windows XP Pro. (The problem also occurs when the OS is Windows Vista.) The following VBA code runs perfectly when I run it in the VBE via F5, displaying both the message "BFROPEN", the message "AFROPEN", and the message "Finished!":
Code: Select all
Application.DisplayAlerts = True
If Dir("C:\Documents and Settings\Mike\IIR\Book1.xlsm") <> "" Then
' Book1.xlsm exists
MsgBox "BFROPEN"
Workbooks.Open ("C:\Documents and Settings\Mike\IIR\Book1.xlsm")
MsgBox "AFROPEN"
Else
MsgBox "PROBLEM"
End If
MsgBox "Finished!"
But if I include the above code in a VBA macro, assign the macro a hot key, and run it by typing the hot key, the message "BFROPEN" is displayed and file Book1.xlsm is opened, but the message "AFROPEN" is not displayed. The message "Finished" isn't displayed either. I suspect that there is something strange about the behaviour of Workbooks.Open when it is an instruction in a macro which is being executed. Any help or suggestions for further debugging will be extremely appreciated. May you have a blessed day.
Sincerely,
Michael D Fitzpatrick
"EvangelMike"
PS: A comprehensive web search of this issue shows that I am not the only one having a problem with Workbooks.Open
Please see:
http://www.excelforum.com/members/ttomlinson.html for desperate but failing attempts to come to grips with this problem. Apparently no instructions following a Workbooks.Open are executed. By the way, workbook Book1.xlsm is a macro enabled workbook with cell A1 set as follows:
[A1] = 1
All the other cells are empty.
