VBA先刷新Power Queries再刷新数据透视表报下载未完成错误求解

现有代码的核心问题 #

• 多余调用ActiveWorkbook.RefreshAll：你已经逐个遍历刷新了所有Power Query连接，这行代码会再次触发所有连接（包括Power Query、数据透视表缓存）的刷新，且没有禁用后台刷新，是报错的核心诱因。
• 等待逻辑冗余且有缺陷：已经将BackgroundQuery设为False时，Connection.Refresh执行时会天然阻塞后续代码，直到当前查询刷新完成，不需要额外加14秒的等待循环。且你设置的最长14秒等待不足以覆盖大查询的刷新时长，同时RefreshCheck定义为String类型和返回的布尔值类型不匹配，容易引发隐性错误。
• 错误捕获逻辑不严谨：On Error Resume Next作用范围过大，非OLEDB类型的连接触发错误后会直接跳出遍历，导致部分Power Query漏刷。
• 没有单独刷新数据透视表缓存：你的需求是Power Query全部刷新完成后再刷数据透视表，不需要用RefreshAll，单独遍历刷新所有数据透视表缓存更可控。

修复后的代码 #

Public Sub UpdatePowerQueries()
    Dim ws As Worksheet
    Dim conn As WorkbookConnection
    Dim pc As PivotCache
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    ' 解除所有工作表保护
    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect
    Next ws
    
    ' 刷新所有Power Query连接
    For Each conn In ThisWorkbook.Connections
        ' 仅处理PQ类型的OLEDB连接
        If conn.Type = xlConnectionTypeOLEDB Then
            If InStr(1, conn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare) > 0 Then
                conn.OLEDBConnection.BackgroundQuery = False
                conn.Refresh
                DoEvents ' 大查询场景下避免Excel假死可保留
            End If
        End If
    Next conn
    
    ' PQ全部刷新完成后，单独刷新所有数据透视表缓存
    For Each pc In ThisWorkbook.PivotCaches
        pc.BackgroundQuery = False
        pc.Refresh
    Next pc
    
    ' 重置工作表位置
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Visible Then
            ws.Activate
            Range("A1").Select
            ActiveWindow.ScrollRow = 1
            ActiveWindow.ScrollColumn = 1
        End If
    Next ws
    
    ' 恢复工作表保护
    For Each ws In ActiveWorkbook.Worksheets
        ws.Protect
    Next ws
    
    Worksheets("Table Of Contents").Activate
    ActiveWorkbook.Save
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

额外校验点 #

• 确认所有Power Query连接的属性中，「刷新此连接时刷新数据透视表」的选项已关闭，避免刷新PQ时连带触发未就绪的透视表刷新。
• 如果有跨工作簿引用的Power Query，需要确认源文件路径可正常访问，权限配置正确。

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