Excel VBA自动运行时Pivot无法刷新问题咨询

问题情况 #

我有个Excel文件包含3个工作表：

• "Qry Results"：查询数据表
• "Qry"：通过公式从"Qry Results"导入数据（公式设置了150行，但实际数据只有100行）
• "Results"：包含基于"Qry"数据的透视表

用VBA刷新"Qry Results"功能正常，"Qry"的数据也能通过公式自动更新，但分步运行VBA时透视表能正常刷新，自动运行时透视表就刷不出来。我用的VBA代码如下：

' Refresh Query    
Sheets("Qry Results").Select
Range("A4").Select
ActiveWorkbook.RefreshAll

' Refresh Pivot
Sheets("Result").Select
Range("A12").Select
ActiveWorkbook.RefreshAll
' 或者用下面这句
' ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

问题原因 #

自动运行时，Excel执行VBA的速度很快，查询刷新完成后，还没等"Qry"工作表的公式完成计算，就立刻触发了透视表刷新，导致透视表读取的是未更新的旧数据。而分步运行时，手动停顿给了公式计算的时间，所以能正常刷新。另外原代码里的Select操作完全没必要，反而会拖慢代码执行，还可能造成不必要的窗口切换。

解决代码 #

修改后的VBA代码会确保查询刷新完成、公式计算完毕后，再刷新透视表：

Sub RefreshAllDataAndPivot()
    ' 禁用后台刷新，确保查询刷新完成后再继续
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False ' 关闭屏幕更新，提升速度
    
    ' 刷新查询表（假设查询表是ListObject格式，强制等待刷新完成）
    Sheets("Qry Results").ListObjects(1).Refresh BackgroundQuery:=False
    
    ' 强制计算所有工作表，确保Qry的公式全部更新
    Application.CalculateFull
    
    ' 刷新透视表
    Sheets("Results").PivotTables("PivotTable1").PivotCache.Refresh
    
    ' 恢复默认设置
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

补充说明 #

• 如果"Qry Results"不是ListObject格式，而是普通查询区域，可以改用ActiveWorkbook.Connections("查询连接名称").Refresh BackgroundQuery:=False，把"查询连接名称"换成你实际的查询连接名
• 关闭屏幕更新不仅能提升运行速度，还能避免自动运行时的窗口闪烁问题

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