Excel VBA 关闭工作簿并触发新宏

Question

我一直在修补这个几个小时，我无法弄清楚。我有一个主工作簿，它启动其他启用宏的模板(生成并保存输出)，然后将输出附加到电子邮件中。 (我不能将它们全部合并到一个工作簿中，因为选项卡、宏和大小的数量会变得过于繁琐)。由于我正在处理的某些事情的 secret 性，我无法分享我的确切代码 - 但我已经缩减/替换了值并提供了我在下面尝试过的代码的“示例”。

我遇到的问题是试图完成以下任务:

MainWB 打开 SubWB1 并调用 (Application.Run) 宏

SubWB1 中的宏生成并保存一个 excel 输出文件

SubWB1 关闭，MainWB 继续下一份报告

我的问题是，一旦我关闭 SubWB1，MainWB 中的宏就会停止运行......我试过了:

1. 尝试 1:在 MainWB 宏中间调用 SubWB1 宏，后续步骤如下:

    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.尝试2:在子WB中的宏末尾调用MainWB中的延续子:

    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. 尝试 3:与上面相同的示例，但将 subWB 分配给 mainWB 中的公共(public)工作簿对象:

    '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.尝试4:从subWB中的BeforeClose事件调用延续宏

    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

注意:除了上面的示例 4 之外，我没有收到任何错误 - 所以代码没有中断，它似乎只是在 MainWB 的“.Close”函数上退出宏，因此不是继续任何进一步的代码。

任何建议将不胜感激!我已经尝试了所有我能想到的方法，但我只能盯着相同的代码行这么久! :)

Answer 1

停止宏的部分是 .Close子WB中的声明。从 subWB 中删除该行并从主中关闭该工作簿:

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