Macro in Excel Stops After A Workbook.Open Command

Introduction

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.

Cause

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.

Resolution

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

#If VBA7 Then
    Declare PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#Else
    Declare Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#End If

Const SHIFT_KEY As Long = &H10

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:\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


Comments

All comments about this page:


Comment by: Barb Reinhardt (13-3-2009 09:49:50) deeplink to this comment

I've used this code in Excel 2003 with great success. Now that I've moved to 2007, it doesn't always work (in the sense that execution ends at unexpected times). When I step through the code and test to see if it's finding the Shift Key pressed, it's found, but I'm thinking there's enough of a delay after the check and before the workbook open that the shift key can be pressed and cause an error. Have you seen this before?

Thanks,
Barb


Comment by: Jan Karel Pieterse (15-3-2009 22:22:33) deeplink to this comment

Hi Barb,

No, I have not seen that one before I'll investigate.
What's even stranger is that with Excel 2007 holding the shift key normally does NOT prevent auto_open code form running (That is a known bug in 2007)!.


Comment by: Arnie (4-5-2009 14:31:41) deeplink to this comment

Comment - If I start a macro that contains Workbooks.Open using a shortcut key (and with VBA editor closed), the macro stops after opening the first file. If I run the macro with the VBA editor open, it runs to completion just fine. There is no dependence on the shift key.

Arnie


Comment by: Jan Karel Pieterse (5-5-2009 10:15:45) deeplink to this comment

Hi Arnie,

What shortcut key have you assigned? Many use control+shift+some key, hence you're still holding the shift key. A workaround is to assign the macro to a toolbar button instead of a hotkey.


Comment by: Phil (20-8-2009 01:34:30) deeplink to this comment

I am running Excel 2003 and have a similar issue as Barb Reinhardt. Despite having the 'wait for no shift' code before I attempt to open a workbook via VBA it still doesn't seem to catch the problem 100% of the time. Most the time I can see my macro pause and wait for shift to be released, but occasionally I do get the situation where after opening a workbook it will just stop. My macro is a consolodation routing, so it involves opening many many workbooks, updating them extracting data from them to a summary, and then moving onto the next.
I too was wondering if the problem was due to the timelapse between testing for there being 'no shift', and when Excel actually gets around to opening the file.
This seems supported by my observation that I don't seem to get the problem on a fast PC with no 'onAccess Antivirus' running. Whereas on my slower laptop that DOES have 'onAccess' running, (so the antivirus software will detect Excel trying to open a file, and will scan the file before then allowing Excel to open it), I seem to get the problem a lot more often.


Comment by: Jan Karel Pieterse (7-9-2009 09:45:42) deeplink to this comment

Hi Phil,

You have a point there, if it takes some time before Excel actually gets handed the file by the system then the test for the shift key may be too early in the process.
Unfortunately there is no way to detect that with VBA.

The best way round that would be to lock down the keyboard altogether. I know there are ways to do that, just don't have the code.


Comment by: Bob (11-9-2009 08:51:18) deeplink to this comment

A commentor named Phil wrote that he is doing a consolidation project where he opens a string of workbooks, extracts data, then moves on. I am doing the same but my problem is that each of the workbooks I am opening has VBA code that is running automatically each time the workbook is opened. My consolidation program therefore gets stopped when the workbook that I open starts it's auto code. I've tried to hold the shift key to not run the auto code but this does not always work. Is there a way to do workbooks.open and tell the workbook being opened to not run it's auto code?


Comment by: Jan Karel Pieterse (11-9-2009 10:13:39) deeplink to this comment

Hi Bob,

Sure, before issuing the Open command, insert this line:

Application.EnableEvents = False

Do not forget to turn them back on, because otherwise all events stay disabled for the entire Excel session:

Application.EnableEvents = True


Comment by: Charmene (28-9-2009 10:46:57) deeplink to this comment

I am having trouble when I open Excel. First I was entering a Macro and I stored it on my personal worksheet and hide it. But now when ever I open Excel it opens with 33 active worksheets. I have no clue as to why or how to get rid of them. I have uninstalled the program and re-installed it but I am getting the same thing. Any ideas?

Thanks


Comment by: Jan Karel Pieterse (28-9-2009 10:49:26) deeplink to this comment

Hi Charmene,

Looks like you have entered a path in Excel's options:

Tools, options, General tab, "At startup, open all files in"

Excel will open all files in that location.


Comment by: Tom Parish (5-11-2009 07:46:19) deeplink to this comment

It took a bit of lateral thinking but based on the above....

Comment by: Phil (8/20/2009 1:34:30 AM)
Comment by: Jan Karel Pieterse (9/7/2009 9:45:42 AM)

....I inserted the loop code right at the start of the procedure and this gave it enough time to register the shift release before doing wb.open. I presume this wouldn't work if my wb.open command was right at the start of the code. Evidently, I need a slower PC.


Comment by: John (25-11-2009 12:42:13) deeplink to this comment

Great idea--thanks for posting it.

Excel is very, very weird sometimes.

Often.


Comment by: Rene Musters (1-4-2010 04:42:05) deeplink to this comment

Guess what, You have identified a long old problem I have, where the open statement stops VB.
HOWEVER, in my situation it is the other way around. I MUST press the shift key to get it working properly.

Here is the code. Any idea WHY? Keyboard works normal, pressing shift provides uppercase, and capslock is uppercase
Never have problem entering logon password etc.

Public Sub OpenFilename(FilePathToOpen)
    ' bug! stops further processing after the open
    ' However, works reverse here. MUST press shift
    Do While Not ShiftPressed()
        DoEvents
        MsgBox "Hold the <Shift> key, and press <Enter>"
    Loop
    Workbooks.Open FileName:=FilePathToOpen
End Sub


Comment by: Jan Karel Pieterse (1-4-2010 04:51:59) deeplink to this comment

Hi Rene,

I have NEVER seen this before!

What Excel version are you using?


Comment by: Adam (18-4-2011 15:45:11) deeplink to this comment

Hi,
I am just wasting a second day on the same problem, macro stops on wb.open - Excel2000 in W7. My macro have a keyboard shortcut Ctrl+Shift+A to run it. I have change it to Ctrl+a and it works! Thank you for an idea with Shift!
Regards


Comment by: Jianing (27-4-2011 15:03:39) deeplink to this comment

Yes, I had the same problem. The Ctrl+Shift shortcut key is the bug. I am using Excel 2003. Not sure this bug is fixed in 2007 or 2010.


Comment by: Roy Clark (17-6-2011 21:27:40) deeplink to this comment

Command button on first workbook opens second workbook. Good. Second workbook requires data from a third workbook to calculate, so I open the third workbook from the second workbook_open event. This has worked great, but today the second workbook refuses to open the third workbook. If I copy the code and trigger it with a command button event, it works perfect, and message boxes throughout indicate the code is running through completely, but for some reason the Open_workbook event on the second workbook refuses to initiate the opening of the third workbook. I have ensured the third workbook is not already open, and I can confirm that the workbook.open command does not think the book is already open with a message box. I am not operating any keys. The only area I changed was to initiate manual workbook recalculation to delay the recalculation of the second workbook till after the third workbook was open so the data was available. I have disabled that and reinstated automatic recalc and confirmed it is running. Any ideas? I am running 2007.


Comment by: Jan Karel Pieterse (18-6-2011 11:10:09) deeplink to this comment

Can you perhaps show the code of the second workbook that is supposed to open the third?


Comment by: Roy Clark (18-6-2011 22:40:28) deeplink to this comment

I completely reconstructed a new workbook, and the problem seems to have gone away for now. I am finding that the procedures in Open_Workbook are fickle, as I do not understand the timing of events, and so we end up easily crashing the program in this procedure (calling events that may not be open yet, or hiding sheets or books that are closing/opening, asking for range names that are not yet registered? etc?), and just one crash seems to permanently disable activeX controls?

Really appreciate your input.Here are copies of two subs, both in the second workbook. The second is in a module.

Private Sub Workbook_Open()
Application.ScreenUpdating = False

'We then mine the Structural Steel workbook path/name from the Global iEngine names
Dim VariableValue As String
VariableValue = GetHiddenNameValue("StructuralSteelModule")

'We now open the workbook listed above
Call OpenTheWorkbookR0003(VariableValue)

'ActiveWindow.Visible = False ' I had to disable this as it will always crash the program!
Application.ScreenUpdating = True
Application.CalculateFull
Application.Calculation = xlCalculationAutomatic

End Sub

The OpenTheWorkbook sub looks like the following:

Public Sub OpenTheWorkbookR0003(FileName1 As String)
Application.ScreenUpdating = False
Dim FilePath1 As String
Dim FullFilePath1 As String
Dim WBook As Workbook

FilePath1 = "C:\Documents and Settings\Owner\Desktop\iEngine\"
On Error Resume Next
'Check to see if file already open
Set WBook = Workbooks(FileName1)
     If WBook Is Nothing Then 'Not open
     Else: Exit Sub
    End If
FullFilePath1 = FilePath1 & FileName1 ' this is for the selected worksheet file path

Err.Clear 'Clear erroneous errors

Set WBook = Workbooks.Open(FullFilePath1, False, False) ' Update links?, Read only?
Application.ScreenUpdating = True

End Sub


Comment by: Jan Karel Pieterse (19-6-2011 23:30:11) deeplink to this comment

Hi Roy,

It is a known problem that Excel may not have finished doing all of its chores when the Workbook_Open event fires, leading to unexpected errors in your code. A workaround for this is to move all of the code *inside* the open event to a separate sub in a normal module.
Then in the Workbook_Open event, you use the Application.Ontime method to call the remainder fo the code you want executed when the file was opened. This gives Excel the opportunity to finish everything it needs to do during startup:


Private Sub Workbook_Open()
Application.Ontime Now, "ContinueOpen"
End Sub



Then in a normal module:

Sub ContinueOpen()
'Real workbook_Open code goes here
End Sub


Comment by: Shivam Srivastava (22-9-2011 04:07:07) deeplink to this comment

I am running a code which is to create hyperlinks on selected cells.The problem is when the destination worksheet is opened and the macro is running for the first time on the fresh destination sheet it does not link but when the macro is rum for the second time on wards the linking is perfect.I can not understand the reason, I want it to run in the first time perfectly.


Comment by: Jan Karel Pieterse (22-9-2011 07:46:36) deeplink to this comment

Hi Shivam,

Maybe the trick I showed Roy (just above your message) helps for your problem too?


Comment by: Vince (1-11-2011 16:12:50) deeplink to this comment

When I open excel from the icon, it opens OK but then launches VBA, how can I stop this


Comment by: Jan Karel Pieterse (2-11-2011 08:02:58) deeplink to this comment

Hi Vince,

Maybe this is caused by an Add-in or something like it?
check out:
www.jkp-ads.com/articles/startupproblems.asp


Comment by: Jim (24-1-2012 16:19:22) deeplink to this comment

Thanks a ton, Jan! Your solution worked great for my problem that was driving me nuts.


Comment by: Larry (23-7-2012 23:17:17) deeplink to this comment

I tried the following code in my macro:

Application.OnTime (Now,"MyWorkbookOpen")

and I got the following error:

"Compile Error: Syntax Error"

I have tried modifying this code in many ways, including syntax similar to this:

Application.OnTime When:="15:55:00", Name:="Macro1"

which I found on a Microsoft website.

I am using Excel 2003. Any suggestions are appreciated!


Comment by: Jan Karel Pieterse (7-8-2012 11:30:16) deeplink to this comment

Hi Larry,

The syntax is:

Workbooks.Open Now(), "MyWorkbookOpen"


Comment by: Salim Talal (4-6-2013 17:58:09) deeplink to this comment

Hallo guys,

Please help me out in this. I have a VB project that opens Excel workbook and an auto-run VBA macro executes according to the plan. The first time when invoked it opens perfectly and all the forms from the workbook displayed ok although the workbook itself is invisible running in the background. Now I close all the forms, the workbook is still running in the background, I invoke the macro to auto-run again from the VB form but nothing happens. How can I auto-run the macro of an already opened workbook using VB.

This is part of me code in a VB project:

In a standard module I have;


Public appXL As Excel.Application
Public wbkXL As Excel.Workbook


In a Form module I have;

Private Sub cmdOpen_Click()
    Dim myPath As String
    
    ' This part of code is to invoke the macro of an
    ' already opened workbook to run it again unfortunately
    ' it does not work
    If Not wbkXL Is Nothing Then
    
        ' This is the macro in the workbook which we need it to run
        appXL.Run wbkXL.Name & "!DataEntry"
        Exit Sub
    End If
    
    ' If the workbook is not opened then we get its path 1st.
    myPath = Dir(App.Path & "\General Inventory Report - Version 4B.xls")
    
    ' Here we alert the user if the file we are looking for is missing
    If myPath = "" Then
        MsgBox "The file : General Inventory Report - Version 4B.xls" _
            & vbNewLine & "you are trying to access does not exists." _
            , vbCritical, "Missing File"
    Else        
        ' Here we get to start the file if found
        ' we start a new Excel application            
        Set appXL = CreateObject("Excel.Application")
        appXL.Workbooks.Open myPath
        
        ' This part runs perfectly    
        Set wbkXL = appXL.ActiveWorkbook
        wbkXL.RunAutoMacros xlAutoOpen
    End If
End Sub


Comment by: Jan Karel Pieterse (4-6-2013 20:15:56) deeplink to this comment

Hi Salim,

You may need to add apostrophes around the filename, like so:

appXL.Run "'" & wbkXL.Name & "'!DataEntry"


Comment by: Salim Talal (5-6-2013 18:54:35) deeplink to this comment

Hi! Jan Karel Pieterse,

A thousand thanks for your humble help. I appreciate a lot for your assistance thank you so so much!


Comment by: Sew (11-6-2013 17:07:28) deeplink to this comment

Hi, I'm trying to do a simple workbook open in excel 2007 with the following code:

Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book1.xls"

The problem is that every time I run this code, I get a run-time error 1004. I've run this before in the past with excel 2003 and I've never had an issue. What am I doing wrong?

Thanks in advance!


Comment by: Jan Karel Pieterse (11-6-2013 21:13:49) deeplink to this comment

Hi Sew,

First thing to check is whether that path and workbook name is really correct and the file is actually there.


Comment by: Sew (11-6-2013 21:19:57) deeplink to this comment

Thank you for the response. I was able to figure it out. When I last wrote the code, I was using .xls files. Excel 2007 saves files in .xslx. This gave the run-time error 1004 because I was referring to workbook.xls instead of workbook.xlsx.


Comment by: mohd hamdan (13-10-2013 07:37:19) deeplink to this comment

Hi

actually i have some excel sheets which they are protected, i found that other people are able to break the password with the “password breaker” well known code.

is there any way i can prevent them from breaking my sheets.

appreciate your help

thanks


Comment by: Jan Karel Pieterse (13-10-2013 16:56:43) deeplink to this comment

Hi Mohd,

Setting a password that is needed to be able to open the file is relatively secure compared to the worksheet passwords. But nothing is so secure that it cannot be cracked.


Comment by: Brian K (26-10-2013 08:25:50) deeplink to this comment

Hi,

I have a code will read a xxx.txt from my web server and oepn the workbook by "Workbooks.Open(FileName:=path & File)", it is fine in XP + Office 2003, but now in WIN7 + Office 2010, it is a long time waiting to download that text file (xxx.txt), would you please help, how can I fix it ?


Comment by: Jan Karel Pieterse (27-10-2013 17:43:06) deeplink to this comment

Hi Brian,

Excel 2010 has stronger security regarding getting data from Web locations. I'm afraid I can't really help to improve the performance here.

Perhaps using a bit of VBA code that first downloads the file and then opens it from the hard disk?


Comment by: Akshat Bhatt (26-11-2013 07:43:41) deeplink to this comment

Hi,

There is a macro been developed in an excel, problem is when I run that macro all other excel files gets open from where data is getting populated. I know this is happening due to the use of Workbook.Open command. But thing is I have to stop other files fom getting opened while I execute the code. How do I disable Workbook.Open command.


Thanks & Regards
Akshat


Comment by: Jan Karel Pieterse (26-11-2013 07:55:09) deeplink to this comment

Hi Akshat,

Without seeing (part of) your code it is impossible to answer your question.


Comment by: Martin (27-6-2014 11:47:45) deeplink to this comment

Hello Jan-Karel,

I hope you can help me because I'm at my wits' end.

This is all in an XP/XL2003 environment

A macro of mine dies as soon as it has opened an external .xls file using workbooks.open. What I mean is that after the debugger has executed the workbooks.open statement, the opened file stays open in Excel, but the debugger is no longer active and the call stack is empty. The problem is definitely not related to the shift button being pressed. It makes no difference if I open the file as read-only, add various 'on error' statements, or remove the workbook_open event from the target file.

What I do notice is that the opened excel file contains a bunch of "#NAME" errors, which only disappear when calculate full is executed using Ctrl-Alt-F9. If I open the external file directly (not using workbooks.open), the #NAME errors appear at first, and then go away automatically when Excel recalculates. The same thing happens if I save the external file after recalculation and re-open it.

I suspect that workbooks.open is bugging out on opening the file and finding the #name errors (or whatever caused them).

I'm not sure what is causing the #Name problem, although the external file has a lot of UDFs and Macros, including UDFs that evaluate names that refer to cells with UDFs in their formulas. Every so often I need to calculate.full to avoid #Errors when editing the external file.

Because the excel macro dies, I can't automate any processing of the target file in Excel 2003. If I run the macro in Excel 2010, it does not die after opening the same external file using workbooks.open, and everything works normally. Unfortunately the users of both sheets have XL 2003 and I can't change this.

Do you have any ideas for a workaround or a solution? Are you aware of any differences between workbooks.open in XL2003 and XL2010?

Many thanks in advance for having a look at this for me, I really appreciate it.

yours sincerely
Martin


Comment by: Jan Karel Pieterse (27-6-2014 14:45:26) deeplink to this comment

Hi Martin,

I strongly suspect one (or more) of the UDF's run into a runtime error (which do not show up when the UDF is called from a worksheet cell or a conditional formatting formula!!).

UDFs require very strict design to make sure no runtime errors occur EVER when they are called. A RT error in a UDF can also cause the calculation chain to break and hence stop Excel from completing the calculation.

A simple way to test is to include an on error resume next at the top of each UDF.

Also, make sure none of the UDFs tries do do things UDFs are not allowed to do.

E.g. you are not allowed to change any formatting of cells and such, nor are you allowed to use the HasFormula property of a passed range object.


Comment by: Martin (27-6-2014 15:43:59) deeplink to this comment

Hello Jan-Karel,

for checking the UDFs, the issue is that the UDFs in turn call a great variety of VBA functions. Wouldn't I have to put an error check in each one of these too? Also, how would an 'on error resume next' help detect a RT-error?

Why would the workbooks.open problem not occur under XL2010 if it's caused by a UDF RT-error? Is it simply because having a UDF RT-error causes arbitrary behaviour?

I will try to chase down if that is, in fact, the cause of the problem and let you know. If anything else crosses your mind in the meantime, do please let me know


Comment by: Jan Karel Pieterse (27-6-2014 16:05:15) deeplink to this comment

Hi Martin,

The on error resume next was meant as a quick check to see if it alleviates the problem. Of course after that you would have to start finding out the root cause.

Indeed: RT errors triggered from a UDF can cause unexpected behavior. Excel 2010's calc engine is different from 2003's so I'm not surprised it behaves different in cases like these.


Comment by: Amar (21-10-2014 14:57:30) deeplink to this comment

Hi,
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.

Regards,
Amar


Comment by: Jan Karel Pieterse (21-10-2014 15:32:45) deeplink to this comment

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 (19-11-2014 21:09:07) deeplink to this comment

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
        DoEvents
    End If


Any way to slow or pause the process?

Thanks for your help,
Natasha


Comment by: Jan Karel Pieterse (20-11-2014 10:37:07) deeplink to this comment

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
        Next
        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
        Next
    Next
    For Each oSh In Worksheets
        For Each oPt In oSh.PivotTables
            oPt.RefreshTable
        Next
    Next
End Sub


Comment by: Hasan (2-4-2015 09:51:40) deeplink to this comment

Hi,

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 workbook.open / 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 workbooks.open 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.

Hasan
(using Win7 / XL2007)


Comment by: Jan Karel Pieterse (2-4-2015 10:28:07) deeplink to this comment

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 (26-3-2016 04:45:13) deeplink to this comment

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 (27-3-2016 20:20:55) deeplink to this comment

Hi Hung,

You did not include your macro?


Comment by: Oliver Cortinas (14-6-2018 16:13:41) deeplink to this comment

Hi, i have that problem but the issue is different, i am running a macro on an excel file, the macro keeps running and then i try to open a new empty spreadsheet from excel, when the new file is open the macro that was running on the other file is closed, any idea why and if there is a solution to keep running macro even if someone open a new spreadshhet of excel?


Comment by: Jan Karel Pieterse (14-6-2018 16:22:42) deeplink to this comment

Hi Oliver,

Can you show the running code?


Comment by: Anne D Wilson (17-3-2020 11:56:00) deeplink to this comment

Wonderful! I had a macro which opened a worksheet and just stopped. I wasted many hours trying to identify the cause before reading this article. I put a DoEvents statement before the instruction and everything was OK. Thankyou!

i = DoEvents
Workbooks.Open Filename:=wrkbknminc


Comment by: Paul Vedamuttu (1-6-2020 20:47:00) deeplink to this comment

Same for me; ...Open Filename:= "WB.xlsx" worked one day and stopped the next ! Just added i = DoEvents and functions resumed. Weird.


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: www.eileenslounge.com.




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