工作簿打开时Power Query刷新完成后如何关闭用户窗体

背景 #

我有一个包含约5个Power Query的Excel工作簿，设置为保存时卸载数据、打开时自动刷新，该设置通过Power Query本身完成——要求即使移除VBA，工作簿的基础功能仍可正常运行。不过当VBA可用时，我可以添加优化用户体验的功能。

我制作了一个按钮，点击可刷新所有查询，刷新时显示用户窗体，完成后关闭窗体并弹出提示框。手动刷新的VBA代码如下：

Private Sub Btn_Refresh_aLL_Click()
'********************************************************************************
'This sub refreshes all data links.  Does the same job as pressing refresh all
'in the data tab of the ribbon.  Set Userform1 Showmodal to False to all code
'to be continued to be processed.
'********************************************************************************
    
    UserForm1.Show
    ThisWorkbook.RefreshAll
    Unload UserForm1
    MsgBox "Database refresh complete"
    
End Sub

将ShowModal = False设置完成后，这段代码运行正常。

问题 #

我希望在Workbook_Open事件触发时显示该用户窗体，待Power Query的自动刷新完成后关闭它，但不知道如何判断刷新完成的时机。手动刷新时可在显示和关闭代码间嵌入刷新命令，但自动刷新没有对应的命令行可嵌入。

已尝试方案 #

我在Workbook_Open中加入消息框测试刷新触发顺序，发现Workbook_Open会先执行，消息框会阻止Power Query开始刷新。还参考过相关问题，但那些都是VBA主动调用刷新的场景，与我这种Power Query自动刷新的情况不同，且内容较难理解。测试代码如下：

Private Sub Workbook_Open()
    MsgBox "Wait for databases to load." & vbCr _
        & vbCr _
        & "Monitor progress in the bottom" & vbCr _
        & "right corner of the Excel status bar."
End Sub

更新 #

所有Power Query的属性设置如图所示：

需求 #

如何判断Power Query在工作簿打开时的自动刷新已完成，从而关闭Workbook_Open中显示的用户窗体？

解决方案 #

可以通过轮询检查所有Power Query的刷新状态实现需求，具体步骤如下：

1. 修改Workbook_Open事件代码 #

在工作簿打开时显示无模态用户窗体，并启动定时检查子程序：

Private Sub Workbook_Open()
    ' 以无模态方式显示用户窗体，避免阻塞后续代码
    UserForm1.Show vbModeless
    ' 启动定时检查，每隔1秒验证一次刷新状态
    Application.OnTime Now + TimeValue("00:00:01"), "CheckRefreshStatus"
End Sub

2. 编写刷新状态检查子程序 #

遍历所有Power Query查询、绑定的ListObject以及数据模型，判断是否全部完成刷新：

Sub CheckRefreshStatus()
    Dim qry As WorkbookQuery
    Dim lo As ListObject
    Dim allRefreshed As Boolean
    allRefreshed = True
    
    ' 检查所有Power Query查询的刷新状态
    For Each qry In ThisWorkbook.Queries
        If qry.Refreshing Then
            allRefreshed = False
            Exit For
        End If
    Next qry
    
    ' 检查所有绑定到工作表的ListObject对应的QueryTable状态
    If allRefreshed Then
        For Each lo In ThisWorkbook.Worksheets
            If Not lo.QueryTable Is Nothing Then
                If lo.QueryTable.Refreshing Then
                    allRefreshed = False
                    Exit For
                End If
            End If
        Next lo
    End If
    
    ' 若使用数据模型，额外检查模型刷新状态
    If allRefreshed Then
        If ThisWorkbook.Model.IsRefreshing Then
            allRefreshed = False
        End If
    End If
    
    If allRefreshed Then
        ' 所有刷新完成，关闭窗体并提示用户
        Unload UserForm1
        MsgBox "数据库自动刷新完成"
    Else
        ' 未完成则继续定时检查
        Application.OnTime Now + TimeValue("00:00:01"), "CheckRefreshStatus"
    End If
End Sub

3. 注意事项 #

• 确保用户窗体的ShowModal属性已设置为False，或通过vbModeless参数显示，否则定时检查代码无法正常执行。
• 若你的Power Query仅加载到数据模型而非工作表，可跳过ListObject的检查步骤；反之则需保留，避免遗漏状态判断。

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