Excel file won't be opened while macro is running.

Question

I have a long-running macro, which takes from several minutes to even an hour for executing.

I found that while the macro is running, the excel file cannot be opened.

All the excel files will only be opened after the execution of macro.

(I have DoEvents in my macro, but it does not help.)

Is there any solution to have an excel file open while macro keeps running?

Now what I can do is let the user stop the macro temporarily by hand if he/she wants to open a file.

However, it is really annoying and inconvenient.

I am finding a way to automatically catch the open event and let user use excel just like no macro is running...

hansvogelaar · Answer

cindy_lu&nbsp;
You can let users open a workbook (or workbooks) from the macro by using
Application.Dialogs(xlDialogOpen).Show
or you can prompt them to select a file by using
    Dim vFile As Variant
    Dim wBook As Workbook
    vFile = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*")
    If vFile = False Then
        ' No workbook selected
    Else
        Set wBook = Workbooks.Open(vFile)
    End If

cindy_lu · Answer

Thanks, this indeed make the file open once I embed this function as a button in my userform.
However, I would expect this problem be solved by the application itself. Not to compromise it with VBA codes...

hansvogelaar · Answer

cindy_lu&nbsp;
I'm afraid it doesn't work that way...

visolanki10 · Answer

cindy_lu&nbsp;&nbsp;You can start another instance of Excel application from the start menu and open the files from there.

Forum Discussion

Excel file won't be opened while macro is running.

Share

Resources