Closing a workbook from an other one without stopp

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 :

1. I have a code in workbook WB1.xlsm called "Code1" which opens WB2 the following way Workbooks.Open("WB2.xlsm").RunAutoMacros(xlAutoOpen)

2. 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 ?

Answer 1

Make sure you call the code in WB2 using the OnTime method. Suppose the routine in WB2 you want to start is called ContinueOpen:

Sub OpenWB2AndDoStuff()
    Workbooks.Open("WB2.xlsm")
    Application.Ontime Now, "'" & ActiveWorkbook.FullName & "'!ContinueOpen"
End Sub

Answer 2

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.

A workaround could be hiding WB1 instead of closing (e.g. minimize it). And run close of WB1 as the very last action of autorun in WB2. This still aborts the autorun of WB2 but it doesn't matter because it was the last action.

Answer 3

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.