使用RefreshAll或.PivotCache.Refresh后数据透视表未更新的VBA问题

问题背景 #

搭建VBA + PowerQuery + SAP ERP数据仪表盘，流程为：

• 从SAP ERP提取数据复制粘贴至Excel表格
• 通过PowerQuery处理数据后，将输出作为数据透视表的数据源

但粘贴SAP数据后，执行ActiveWorkbook.RefreshAll代码无法触发数据透视表更新，尝试过常规教程及Stack Overflow方案未解决，相关代码与文件已上传至GitHub仓库。

针对性解决方案 #

1. 拆分刷新流程：先等PowerQuery完成再更新透视表 #

RefreshAll采用异步执行逻辑，可能数据透视表在PowerQuery未完成数据处理时就启动刷新，导致获取的仍是旧数据。改用以下VBA代码强制同步刷新：

Sub RefreshPQThenPivot()
    ' 遍历并刷新所有PowerQuery连接，等待刷新完成
    Dim conn As WorkbookConnection
    For Each conn In ThisWorkbook.Connections
        If conn.Type = xlConnectionTypeOLEDB And InStr(conn.OLEDBConnection.CommandText, "Power Query") > 0 Then
            conn.Refresh
            Do While conn.Refreshing
                DoEvents ' 释放系统资源，避免假死
            Loop
        End If
    Next conn
    
    ' 逐一刷新所有数据透视表
    Dim ws As Worksheet
    Dim pt As PivotTable
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

2. 验证数据透视表数据源绑定 #

确认数据透视表的数据源是PowerQuery输出的结构化表，而非固定单元格区域。若为静态区域，需重新绑定透视表到PowerQuery生成的表对象：

• 选中数据透视表 → 「分析」选项卡 → 「更改数据源」→ 选择PowerQuery输出的表

3. 禁用PowerQuery连接的后台刷新 #

调整PowerQuery连接属性，确保刷新同步完成：

• 「数据」选项卡 → 「连接」→ 选中PowerQuery对应的连接 → 「属性」
• 取消勾选「允许后台刷新」

4. 调整代码执行顺序 #

在粘贴SAP数据的代码末尾，直接调用RefreshPQThenPivot宏，替换原有的ActiveWorkbook.RefreshAll，确保数据粘贴完成后再执行刷新流程。

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