Excel VBA Close Workbook and Trigger New Macro

Question

I've been tinkering with this for hours and I can't figure it out. I've got a main workbook that launches other macro-enabled templates (which generate and save outputs) and then attaches the outputs to an email. (I can't combine them all into one workbook because the number of tabs, macros, and size would become far too cumbersome). Due to the confidential nature of some of the things I'm working with, I can't share my exact code - but I've trimmed down/replaced values and provided "examples" of the code I've tried below.

The issue I'm having is in trying to accomplish the following:

MainWB opens SubWB1 and calls (Application.Run) a macro
Macro in SubWB1 generates and saves an excel output file
SubWB1 closes and MainWB continues with next report

My problem is that, once I close the SubWB1, the macros within the MainWB stop running... I've tried:

1. Try 1: Calling the SubWB1 macro in the middle of a MainWB macro with the continuation steps after:

    Sub AfternoonReport () 'macro in MainWB
        'do stuff
        Workbooks.Open FileName:="C:\Users\me\Documents\DailyReport\submacro1.xlsm"
        Application.Run "'submacro1.xslm'!triggerFromMain" 'macro ends with .close, save changes false
      '************STOPS HERE***********
        Application.Wait Now + TimeValue("00:00:03")
        generateEmail 'macro within MainWB
    End Sub

2. Try 2: Calling a continuation sub within the MainWB at the end of the macro in the subWB:

    Sub AfternoonReport () 'macro in MainWB
        'do stuff
        Workbooks.Open FileName:="C:\Users\me\Documents\DailyReport\submacro1.xlsm"
        Application.Run "'submacro1.xslm'!triggerFromMain"
    End Sub

    Sub triggerFromMain () 'macro in subWB
        'do stuff
        Application.Run "'mainWB.xslm'!continueFromSub1"
    End Sub

    Sub continueFromSub1 () 'macro in MainWB
        'do stuff
        Workbooks("submacro1.xlsm").Close SaveChanges:=False
      '************STOPS HERE***********
        Application.Wait Now + TimeValue("00:00:03")
        generateEmail 'macro within MainWB
    End Sub

3. Try 3: The same example above, but assigning the subWB to a public workbook object in the mainWB:

    'Declarations in mainWB
    Public subWBobj As Workbook

    Sub AfternoonReport () 'macro in MainWB
        'do stuff
        Set subWBobj = Workbooks.Open FileName:="C:\Users\me\Documents\DailyReport\submacro1.xlsm"
        Application.Run "'submacro1.xslm'!triggerFromMain"
    End Sub

    Sub triggerFromMain () 'macro in subWB
        'do stuff
        Application.Run "'mainWB.xslm'!continueFromSub1"
    End Sub

    Sub continueFromSub1 () 'macro in MainWB
        'do stuff
        subWBobj.Close SaveChanges:=False
      '************STOPS HERE***********
        Application.Wait Now + TimeValue("00:00:03")
        generateEmail 'macro within MainWB
    End Sub

4. Try 4: Calling the continuation macro from the BeforeClose event in the subWB

    Sub AfternoonReport () 'macro in MainWB
        'do stuff
        Workbooks.Open FileName:="C:\Users\me\Documents\DailyReport\submacro1.xlsm"
        Application.Run "'submacro1.xslm'!triggerFromMain"
    End Sub

    Sub triggerFromMain () 'macro in subWB
        'do stuff
        ThisWorkbook.Close SaveChanges:=False
    End Sub

    Sub Workbook_BeforeClose () 'macro in subWB
        Application.Run "'mainWB.xslm'!continueFromSub1"
      '********Doesnt allow the workbook to close and just fires macros in the mainWB - causing other issues*******
    End Sub

Note: In all but example 4 above, I don't receive any errors - so the code isn't breaking, it just seems to be exiting the macro in the MainWB on the ".Close" function, and then therefore isn't continuing with any further code.

Any suggestions would be greatly appreciated! I've tried everything I can think of and I can only stare at the same lines of code for so long! :)

PeterT PeterT · Accepted Answer · 2020-03-25T13:49:42

The part that's stopping your macro is the .Close statement in the subWB. Remove that line from the subWB and close that workbook from the main:

Option Explicit

Sub AfternoonReport()
    Dim msg As String
    msg = "Macro executed at " & Format(Now(), "dd-mmm-yyyy hh:mm")
    Debug.Print "from mainWB: " & msg

    Dim otherWB As Workbook
    Set otherWB = Workbooks.Open(Filename:="C:\Temp\submacro1.xlsm")
    Application.Run "'submacro1.xlsm'!triggerFromMain" 'macro ends with .close, save changes false

    Debug.Print "back from the sub macro and we're done."

    otherWB.Close SaveChanges:=False
End Sub

Excel VBA Close Workbook and Trigger New Macro

1 Answers