Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Workbook Open Bug

Macro in Excel Stops After A Workbook.Open Command


Sometimes Excel VBA behaves unexpectedly. Here is an example:

You are running code that is meant to open one or more files using the Workbooks.Open command. As soon as you hold down the shift key when this command is being processed, macro execution stops and your program is terminated entirely. This will even happen if you are e.g. editing an email message in Outlook whilst Excel VBA is processing the code in the background.


Excel is designed not to run Auto_Open and Workbook_Open code when a workbook is opened from the User interface whilst holding down the shift key. Unfortunately, this (desired) behaviour also applies when opening workbooks through VBA code.

Microsoft has confirmed this is a bug.


Before opening a workbook through code, detect if the shift key is pressed and wait until it is released. The code sample below shows you how to do this:

 Option Explicit

'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()
    Workbooks.Open Filename:="C:\My Documents\ShiftKeyDemo.xls"
End Sub

This problem has also been listed in the Microsoft knowledge base:

Macro in Excel Stops After A Workbook.Open Command, February 26, 2005


Showing last 8 comments of 51 in total (Show All Comments):


Comment by: Amar (10/21/2014 2:57:30 PM)

I was working on a macro and it was working perfectly fine.Later I had to get my laptop formatted and then when I run the macro it throws me an error saying...Run time error 1004. Microsft Office Excel cannot access the file 'C:\3EA66200'.There are several possible reasons.1) The file name or path doesnt exists.2) The file is being used by another program.3) The workbook you are trying to save has the same name as the currently opened workbook.



Comment by: Jan Karel Pieterse (10/21/2014 3:32:45 PM)

Hi Amar,

Looks like Excel tries to save in the root fo drive C, which normally is a protected folder (you would need administrator rights to write files in that location). Perhaps you need to change directory first?


Comment by: Natasha (11/19/2014 9:09:07 PM)

I stumbled upon this page because I wondered if I could put in a pause of some sort after using Workbooks.Open.
I have an excel (.xlsm) that gets opened by a program at certain times by a service running on a server. This is to email reports. One "summary" report excel opens 3 other xlsm's to get data. 2 of these xlsm's are a little heavy and do some calculations and take some time before they get populated with current info.

Here's an example of the VBA for opening one of them:

    LogInformation ("Opening Something")
    Set Something = Workbooks.Open("\\SERVER\folder1\folder2\BigExcelWithLotsOfCalculations.xlsm")
    Something.Application.DisplayAlerts = False
    LogInformation ("Something Open")
    If Not Something.Application.CalculationState = xlDone Then
    End If

Any way to slow or pause the process?

Thanks for your help,


Comment by: Jan Karel Pieterse (11/20/2014 10:37:07 AM)

Hi Natasha,

Normally, VBA code is stalled during calculations, you should not have to do anything to ensure calcs are done.
However, refreshes of external connections which are set to refresh on open are done asynchonously and hence can cause a problem.

What can be done to circumvent this is to turn off refresh on open for all connections and handle the refreshes using VBA, making them all synchronous by using the BackGroundQuery property of the Refresj method and setting it to false explicitly:

Sub Refreshall()
    Dim oPt As PivotTable
    Dim oQt As QueryTable
    Dim oSh As Worksheet
    Dim oLo As ListObject
    For Each oSh In Worksheets
        'Querytabellen die in oudere Excel versies gemaakt zijn
        'staan NIET in een tabel, evenals web queries
        For Each oQt In oSh.QueryTables
            oQt.Refresh False
        For Each oLo In oSh.ListObjects
            On Error Resume Next
            Set oQt = oLo.QueryTable
            On Error GoTo 0
            If Not oQt Is Nothing Then
                oQt.Refresh False
            End If
    For Each oSh In Worksheets
        For Each oPt In oSh.PivotTables
End Sub


Comment by: Hasan (4/2/2015 9:51:40 AM)


I ran into this issue while trying to re-open 'ThisWorkbook' (i.e the caller-workbook from its own code).

I need to revert the file back to last saved version within the before-close event I trapped, where the user choses not to save the changes. As my code hides all sheets but one before close, I need to save the file though, but I can do this only for the last-saved-version.

I thought this issue of / auto_open not working was related to opening the 'caller-workbook' but as you describe it might not be the real source.

Also any code after the command in the caller-workbook do not get executed, so I have no way of executing other commands (except with application.onTime which I don't want to use, as it does not work when app is closed and not only the wbk).

Unfortunaly in my case it has nothing to do with Shift key so checking if pressed will not help.

If you have any ideas how to work around, please let me know.

(using Win7 / XL2007)


Comment by: Jan Karel Pieterse (4/2/2015 10:28:07 AM)

Hi Hasan,

I guess what you could try to do is:

- Save-as to a temporary name in a fixed location
- Open the previous copy
- Close the temporary file
- Have some code in the original which tries to kill the temporary file.


Comment by: Hung (3/26/2016 4:45:13 AM)

Please help me. When running below macro, I get the message "400". I don't know how to solution this problem?


Comment by: Jan Karel Pieterse (3/27/2016 8:20:55 PM)

Hi Hung,

You did not include your macro?


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used once to inform you when your comment is published or to respond to your question directly):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].