Excel打开时自动刷新Power Query并更新关联数据透视表的VBA问题

核心问题原因 #

你的原有代码失效是因为Power Query连接默认启用后台刷新，VBA执行到PQ刷新语句后不会等待数据完全写入DATA工作表，就直接运行后续的透视表刷新逻辑，此时透视表读取的依然是未更新的旧数据，自然无法同步。

完整实现方案 #

代码部署要求 #

自动触发文件打开事件的代码必须放在VBA编辑器的ThisWorkbook对象下，不能放在普通模块，否则打开文件时不会自动执行。

完整可运行代码 #

Private Sub Workbook_Open()
    Dim conn As WorkbookConnection
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim originalBgSetting As Boolean
    
    ' 替换为你实际的Power Query连接名称，可在「数据-连接」菜单中查询
    Set conn = ThisWorkbook.Connections("Name of Query")
    
    ' 暂存原有后台刷新配置，临时关闭后台刷新，强制VBA等待PQ刷新完成
    originalBgSetting = conn.OLEDBConnection.BackgroundQuery
    conn.OLEDBConnection.BackgroundQuery = False
    
    ' 刷新指定Power Query
    conn.Refresh
    
    ' 恢复原有后台刷新配置
    conn.OLEDBConnection.BackgroundQuery = originalBgSetting
    
    ' 刷新全表所有数据透视表
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

优化提示 #

如果只需要刷新指定的3个数据透视表，可以替换掉全表遍历的代码，提升执行效率，示例如下：

' 仅刷新指定3个透视表，替换为你实际的工作表、透视表名称即可
ThisWorkbook.Worksheets("工作表1名称").PivotTables("透视表1名称").RefreshTable
ThisWorkbook.Worksheets("工作表2名称").PivotTables("透视表2名称").RefreshTable
ThisWorkbook.Worksheets("工作表3名称").PivotTables("透视表3名称").RefreshTable

内容的提问来源于stack exchange，提问作者Cecilie S. K