如何从另一Excel工作簿运行宏并等待其执行完成？

问题背景 #

我有一个需定期刷新的工作簿Spr1，但每次打开就刷新耗时过长，因此创建了启动工作簿Spr2，通过宏参数启动Spr1。

Spr2原Workbook_Open事件代码：

Private Sub Workbook_Open()
Application.Visible = True
Application.Run "'\\path\Spr1.xlsm'!Refresh_data"
Workbooks("Spr1.xlsm").Close SaveChanges:=True
Application.Quit
End Sub

Spr1原宏代码：

Sub Refresh_data()
ThisWorkbook.RefreshAll
End Sub

当前问题：Spr2的宏未等待Spr1的Refresh_data宏完成刷新，就直接关闭Excel。

解决方案 #

核心是强制刷新操作同步执行，并确保代码等待所有刷新、计算完成后再执行关闭逻辑，以下是具体修改方案：

1. 修改Spr1的Refresh_data宏，确保刷新同步完成 #

默认RefreshAll是异步执行的，需要关闭后台查询并等待计算完成：

Sub Refresh_data()
    Dim conn As WorkbookConnection
    Dim qt As QueryTable
    
    ' 关闭所有ODBC/OLEDB连接的后台查询，强制同步刷新
    For Each conn In ThisWorkbook.Connections
        If conn.Type = xlConnectionTypeODBC Or conn.Type = xlConnectionTypeOLEDB Then
            conn.OLEDBConnection.BackgroundQuery = False
        End If
    Next conn
    
    ' 关闭所有查询表的后台查询
    For Each qt In ThisWorkbook.Worksheets.QueryTables
        qt.BackgroundQuery = False
    Next qt
    
    ' 执行全量刷新
    ThisWorkbook.RefreshAll
    
    ' 等待所有计算任务完成
    Do Until Application.CalculationState = xlDone
        DoEvents
    Loop
End Sub

2. 修改Spr2的Workbook_Open事件，优化调用逻辑 #

使用工作簿对象引用代替名称查找，并增加状态检查：

Private Sub Workbook_Open()
    Application.Visible = True
    Dim spr1WB As Workbook
    
    ' 直接打开并获取Spr1对象，避免名称查找错误
    Set spr1WB = Workbooks.Open("\\path\Spr1.xlsm")
    ' 调用刷新宏
    Application.Run "'" & spr1WB.FullName & "'!Refresh_data"
    
    ' 等待Spr1刷新完成且状态稳定
    Do Until spr1WB.Saved And Application.CalculationState = xlDone
        DoEvents
    Loop
    
    ' 关闭工作簿并退出Excel
    spr1WB.Close SaveChanges:=True
    Application.Quit
End Sub

内容的提问来源于stack exchange，提问作者Tom_pl