VBA批量刷新Power Query失败求助：185个机密Excel文件无法正常更新

问题背景 #

• 185个高度机密Excel文件，每个包含两个Power Query：Query - SF Data和Query - SF Data Totals
• 用VBA批量处理时两个查询均无法完成刷新；手动打开单个文件时，Query - SF Data可正常更新，但Query - SF Data Totals必须执行Refresh All才能生效
• 文件需在2023年11月15日发出，未更新可能导致机密记录泄露，已尝试手动执行Refresh All，但批量VBA处理无效

问题分析 #

第二个查询Query - SF Data Totals大概率依赖第一个查询的输出结果，或其刷新逻辑绑定了Workbook级别的Refresh All触发——单独刷新单个连接不会触发依赖查询的链式更新。原代码中单独刷新两个连接、固定等待15秒的方式无法保证刷新完成，且未处理Power Query的后台刷新状态。

修改后的VBA代码 #

Sub UpdatePowerQuery()

'PURPOSE: 批量刷新指定文件夹中所有Excel文件的Power Query
Dim WB As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim conn As WorkbookConnection

'优化宏运行速度
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
MsgBox "警告：请确认选择正确的文件夹！"

'选择目标文件夹
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
    .Title = "选择目标文件夹"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo ResetSettings
    myPath = .SelectedItems(1) & "\"
End With

'取消选择则退出
If myPath = "" Then GoTo ResetSettings

'设置目标文件扩展名
myExtension = "*.xls*"
myFile = Dir(myPath & myExtension)

'遍历文件夹中所有Excel文件
Do While myFile <> ""
    Set WB = Workbooks.Open(Filename:=myPath & myFile)
    
    '核心刷新逻辑修改
    WB.Queries.FastCombine = True '忽略隐私级别限制
    
    '启用RefreshAll并等待所有刷新完成
    WB.RefreshAll
    '等待每个Power Query连接刷新完成
    For Each conn In WB.Connections
        If conn.Type = xlConnectionTypeOLEDB Then
            Do While conn.OLEDBConnection.Refreshing
                DoEvents '让出CPU资源，避免假死
            Loop
        End If
    Next conn
    
    '保存并关闭文件
    WB.Close SaveChanges:=True
    myFile = Dir
Loop

MsgBox "批量刷新完成！"

ResetSettings:
'恢复Excel默认设置
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True

End Sub

关键修改说明 #

1. 替换为WB.RefreshAll：确保所有存在依赖关系的查询按正确顺序触发刷新，解决第二个查询依赖第一个查询结果的问题
2. 添加刷新等待逻辑：遍历所有连接，等待每个Power Query后台刷新完成后再保存文件，避免因提前关闭导致的更新失败
3. 使用DoEvents：避免宏运行时Excel假死，同时实时检测刷新状态

内容的提问来源于stack exchange，提问作者T-Rex