How to stop Excel from prompting to reopen a workbook?

Question

I have a readonly Excel workbook containing a VBA application. The application saves any data that needs to be saved in a database, and the workbook is always closed without saving (by setting ThisWorkbook.Saved = True in BeforeClose).

I have the following problem:

• User double-clicks on the workbook in Windows Explorer, workbook opens.

• User double-clicks a second time on the workbook in Windows Explorer.

• Excel prompts: "MyWorkbook.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen MyWorkbook.xls?"

• If the user clicks on "Yes", the workbook is reopened without executing the BeforeClose event handler of the instance that was already open.

This is a problem in my application, because it means that some important cleanup code in the BeforeClose event handler does not get executed.

Can anyone suggest a VBA solution to this. Which could be:

• Suppressing the prompt to reopen the workbook. Instead silently use the already-open instance.

• Somehow get the BeforeClose or some other event handler to run in the original instance before it is closed, so I can run my cleanup code.

Update:

This is Excel 2003.

I can get rid of the unwanted prompt by setting "ThisWorkbook.Saved = True" in the Workbook_SheetChanged event handler (the VBA app is responsible for saving any data that needs to be saved in a database, so I don't care about having Excel saving changes).

However this doesn't solve my problem: if I do this, then double-clicking on the Workbook in explorer silently reopens the workbook, but still does this without calling by "BeforeClose" event handler.

So to rephrase the question:

• Is there anyway using VBA to detect and intercept a workbook being reopened in this way?

Update 2

Accepting BKimmel's answer - it does seem there is no VBA way to intercept this event from within the workbook.

The solution I'll implement will be to move the application code into an XLA add-in, which is automatically loaded (if not already loaded) when the workbook is loaded. The add-in can handle Open and BeforeClose events, and store the information it needs to do the clean up.

Answer 1

You can use some special application events to handle when a workbook is reopened and manually fire the Workbook_Close code:

Create a class module called ApplicationController with the following code:

Public WithEvents CApp As Application
Public CurrentWB as Workbook

Private Sub CApp_WorkbookOpen(ByVal wb As Excel.Workbook)


Dim sPathName As String
sPathName = wb.Name
if sPathName = CurrentWB.Name then CallSomeMethod()

End Sub

then on your workbook_load event do something like:

Public controller as new ApplicationController
Private Sub Workbook_Open()
set controller.CApp = Excel.Application
set controller.CurrentWB = ThisWorkbook
End Sub

now when excel goes to open a new workbook you'll capture the event and run your safety method. You could even prevent it reopening the workbook if you wished.

Answer 2

Man...this seems easy but it is a tough one. I toyed around with every event and application/workbook property that I could think of in my 7 or so years of experience working with Excel VBA and I couldn't come up with anything elegant at all... I think the short answer is that there is not really a short elegant solution to this problem (If there is, I would really like to see it). That's the bad news.

The good news is that there I think there are ways to work around the problem...1 is a little bit more elegant, but would require you to rethink some of your methods and the other is dirty, but probably a little bit simpler.

The more elegant way would involve rethinking and refactoring your methods...I think the reason I've never run into this problem in my work is that at the end of each of my methods, the workbook is left in a "good" state... or in other words, if there is "cleanup" to do then it is done at the end of each method. If there is a huge overhead performance hit in this, you might consider setting a boolean flag and restricting whether or not it is run at the end of each of your other methods. (i.e. runCleanup = ReturnTrueIfCleanupIsNeeded() If (runCleanup = true) Then CleanupMethod() End If)

The dirtier way would involve making the entire workbook itself into a kind of "ad-hoc mutex" by
1) copying the entire (original) Workbook at the WorkBookOpen event into another (temp) WorkBook and using the "SaveAs" method on the temp to place it in a seperate location.
2) Storing the (original) path.
3) Adding code to the WorkBook open event that checks for the (temp) and either A) Closes itself immediately and activates temp or B) Overwrites itself with (temp) and then uses SaveAs
4) Catch the event before the user saves the WorkBook and save it to both the original location, and the temp location.
5) At the WorkBookClose event, run any cleanup you need to and save the temp back to its original location.

The point of all this is to side-step the "collision" that happens when you open the workbook twice - essentially, by creating a temp workbook when you open it the first time and saving it in a different location (maybe you could even hide the file?) you are ensuring that when the user clicks the original, it won't collide with the data they are already working with in the application. (Of course, this has it's own problems/questions like "What if I don't know what paths the user has access to save the temp to" or "What if the user opens the temp XLS file...but that's why I called it the dirt way)"

I know that's a lot, especially if you aren't accustomed to working with VBA; If you want me to post some code to help you with any of those steps, leave a comment and I will.

Thanks for the interesting question.