Closing a workbook from an other one without stopping code

Question

Issue : I am encountering the issue that if I try to close a workbook from an other one, the code stops running.

This issue has been exposed in a few posts already but there doesn't seem to have a solution.

What I am trying to do :

I have a code in workbook WB1.xlsm called "Code1" which opens WB2 the following way Workbooks.Open("WB2.xlsm").RunAutoMacros(xlAutoOpen)
The Auto_Open code form WB2 is now running, I do my things in WB2 (copy data) and then the goal will be to paste those data in WB3 and close WB1 so I proceed as the following at the end of the Auto_Open code from WB2 before jumping into WB3:
```
For Each wbk In Workbooks
  If wbk.Name = "WB1.xlsm" Then
    'wbk.Activate
    'DoEvents
    'ActiveWorkbook.Close False
     wbk.Close False
    'Application.OnTime Now + TimeValue("00:00:01"), "wbk.Close"
  End If
Next
Workbooks.Open(PathTo_WB3).RunAutoMacros (xlAutoOpen)
```
The problem is that the code stops after closing WB1 ( above you can see different attempts to close it but they all fail...)

Question : Is it actually possible to close a workbook from an other one without seeing the code being stopped ?

You're closing the workbook that the code is running from if i'm not mistaken? — Tim Wilkinson
You have code (A) that's running code (B), so when you say "the whole code", do you mean A or B? Is that code in WB1? wbk.Close is inside a loop that iterates all workbooks in the ApplicationWorkbooks collection, and that includes the workbook containing the code that's currently executing that loop (i.e. ThisWorkbook) - closing the file that contains the executing code will stop that code, that's just how it is. So please edit to clarify exactly what you mean, we're not looking at your screen and we can't read minds. — Mathieu Guindon
This is a normal behavior. Because the autorun of WB2 is within the context of the sub in WB1 which opened WB2. If you close WB1 within the autorun of WB2 you instantly kill that context which stops the run immediately. VBA can not multithread! — Pᴇʜ
FWIW an Auto_Open macro that closes other workbooks is a very very bad idea. — Mathieu Guindon
You should reconsider your design. If I ever opened an Excel file and it closed everything else I was working on, I'd delete it and never use it again. That's malware - not a macro. — Comintern

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2017-03-14T16:37:06

I have a code in workbook WB1.xlsm called "Code1" which opens WB2

So WB1 is the caller of WB2. Here's a golden rule:

He who opens a file, should be responsible for closing it.

That golden rule applies to many many many things in programming, and if you derive from it you're going to hit many many many problems.

He who creates an object, should be responsible for destroying it.
He who allocates a resource, should be responsible for deallocating it.
...

So you have a file named WB1.xlsm that contains code that's running - as far as execution context is concerned, that WB1 is ThisWorkbook.

So ThisWorkbook does this:

Workbooks.Open("WB2.xlsm").RunAutoMacros xlAutoOpen

That assumes the file opens successfully, and discards a precious object reference that you actually need... badly. Change it to this:

With Workbooks.Open("WB2.xlsm")
    .RunAutoMacros xlAutoOpen
    .Close False
End With

Now the automacros in WB2 can do whatever they like, except attempt to close WB1.

So WB2's automacro opens WB3 - therefore WB2 should also be responsible for closing WB3, because it's responsible for opening it - in WB2.xlsm you would have a Workbook_BeforeClose handler to make sure WB3 closes along with WB2. Or whatever - it's not clear at all why you would need an automacro to do this, or why WB1 can't just open WB2 and WB3.

Just keep your execution context / call stack in mind (the call originated from WB1, so until execution returns to WB1 you have stuff pending), and remember that VBA code lives inside a host document - if you close that document, the code ceases to exist.

Closing a workbook from an other one without stopping code

3 Answers

He who opens a file, should be responsible for closing it.