CODEWITHSUNDEEP
vbaexcelmacros
Mateusz
Mateusz

Reputation: 1

Excel VBA macro accross workbooks not fully executed

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?

Upvotes: 0

Views: 211

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

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

Upvotes: 1

Related Questions