Excel VBA macro accross workbooks not fully executed

Question

I have 6 files that execute macros each after another. The 1st file with VBA code (master file) opens remaining 5 files and initiates VBA chain reaction. The 2nd file does the macro job and initiates job of the 3rd file and it goes until 6th file.

The 6th file then refers to the 1st file's macro (master), which is supposed to close all 5 workbooks (apart from this 1stone). The code of 6th workbook looks like this:

Application.DisplayAlerts = False

ThisWorkbook.RefreshAll
Workbooks("6th_file.xlsm").SaveAs Filename:= _
"[URL]6th_file_htm.htm" 

Application.Run ("refresh_tool.xlsm!CloseAll.CloseAll")

Then it goes to:

Option Explicit

Sub CloseAll()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Dim wb4 As Workbook
Dim wb5 As Workbook

Set wb1 = Workbooks("wb1.xlsm")
Set wb2 = Workbooks("wb2.xlsm")
Set wb3 = Workbooks("wb3.xlsm")
Set wb4 = Workbooks("wb4.xlsm")
Set wb5 = Workbooks("wb5.xlsm")

wb1.Close
wb2.Close
wb3.Close
wb4.Close
wb5.Close

The result is that when run in that chain only the first workbook is closed (refresh_tool, wb2, wb3, wb4 and wb5 remain open).

However, when the CloseAll() is run alone - it works properly and closes all 5 files (only refresh_tool remains open).

I tried make it simple at first with Workbooks("...").Close, but tried several things already, ending up with the code above.

Can anyone help please?

So the code to close the workbooks is in the first workbook? In your Sub CloseAll() code the first workbook is the one that's closed first!! Therefore none of the code beyond wb1.Close will run. — Absinthe
I would strongly recommend not doing it in this "chain reaction" method. Put all the code in a single workbook (whether Book 1 or even a separate master code workbook), and then open & close your other workbooks from the master. — Chris Melville
@Absinthe, no it's not in the wb1, there are 6 files in total. The CloseAll() is located in the "refresh_tool.xlsm", which is, lets say, file wb0. — Mateusz
@ChrisMelville wb2 updates based on wb1. I did try to consolidate the code under one master file, but it wouldn't work properly. Anyways, I will be possibly working on that in future, but now it's just fine. What I am after is the CloseAll() working as it should. Morever, the CloseAll() is indeed located in the master. But CloseAll has to run when job in wb5 is done, so it's initiaded from wb5's code (as presented). — Mateusz

OpiesDad OpiesDad · Accepted Answer · 2016-07-06T22:15:06

This isn't going to work. You need to do as the other users have suggested.

What is happening is this (condensed to only 3 workbooks):

i) Run Macro from refresh_tool to open wb1

ii) wb1 Opens (in thread for refresh_tool macro)

iii) Run Macro from wb1 (in thread for refresh_tool macro)

iv) Macro from wb1 is running (in thread for wb1 which is in thread for refresh_tool)

v) wb2 Opens (in thread for wb1 which is in thread for refresh_tool)

vi) Run Macro from wb2 (in thread for wb1 macro which is in thread for refresh_tool)

vii) Macro from wb2 calls Close macro in refresh_tool (in thread for wb2 which is in thread for wb1 which is in thread for refresh_tool)

viii) Close macro from refresh_tool starts running (in thread for wb2 which is in thread for wb1 which is in original thread for refresh_tool)

ix) Close macro from refresh_tool closes wb1

THIS IS THE PROBLEM!!!!!! This closes the thread for wb1, which ends the running of the close macro in the refresh tool. So, it never gets past the first close. Even if you reordered how the workbooks were closed, the close macro would be running in the threads for ALL of the workbooks at this point. Whichever one you close ends the thread for that workbook, which ends the Close macro.

You need to do what was suggested in the comments and run all the code from one master. Alternatively (and I would recommend the other way), close each workbook at the end of each macro within the workbook if you feel you really must chain these:

In wb1:

Public Sub openFileandRun()

Dim wb As Workbook

Set wb = Workbooks.Open("filepath\wb2.xlsm")
Application.Run (wb.Name & "!openModule.openFileandRun")
Thisworkbook.Close SaveChanges:=False

End Sub

In wb2:

Public Sub openFileandRun()

Dim wb As Workbook

Set wb = Workbooks.Open("filepath\wb3.xlsm")
Application.Run (wb.Name & "!openModule.openFileandRun")
Thisworkbook.Close SaveChanges:=False

End Sub

Etc.

Obviously, fix the names of the modules and subroutines to match yours.

EDIT

Alternatively, you could just call "Close_All" from the original master file. Just do the call after the first opening of the code:

Public Sub masterRun()

Dim wb As Workbook

Set wb = Workbooks.Open("filepath\wb1.xlsm")
'Do whatever you are doing to wb1.

Call CloseAll

End Sub

Excel VBA macro accross workbooks not fully executed

1 Answers