excel不刷新所有数据透视表 [英] excel not refreshing all pivot tables

问题描述

我正在尝试使用vba刷新数据透视表.
我有修改底层表查询并刷新查询的代码.
完成此操作后，它将刷新数据透视表，这又将刷新摘要表.由于某些原因，最终的数据透视表未刷新.它会正常刷新.

I am trying to refresh pivot tables using vba.
I have code that modifies the underlying table query, and refreshes the query.
Once that is done, it refreshes the pivot tables, which in turn will refresh a summary sheet. For some reason, the final pivot table is not being refreshed. It will refresh normally.

尝试的方法:

1.

Dim PC As PivotCache
For Each PC In ThisWorkbook.PivotCaches
    While DateDiff("s", PC.RefreshDate, Now) > 60
    PC.Refresh
    Wend
Next

2.

Sheets("Pivots").PivotTables("Pivot1").PivotCache.Refresh
Sheets("Pivots").PivotTables("Pivot2").PivotCache.Refresh
Sheets("Pivots").PivotTables("Pivot3").PivotCache.Refresh
Sheets("Pivots").PivotTables("Pivot4").PivotCache.Refresh

3.

ThisWorkbook.RefreshAll

在刷新数据透视表之前，在刷新数据表之后，我尝试包含DoEvents.
我也尝试过将文件复制到另一个文件，然后在该文件中重新创建.效果保持不变，它只是移动到它认为是最后一个创建的数据透视表.
xlsb文件大小为279Kb，xlsm文件大小为336 Kb.
这些表的数据来自SQL Server 2012，并且所有表都在同一张Excel工作表中.这些表已更新，但枢轴未更新.

I have tried including a DoEvents before refreshing pivot tables, after the refresh of the tables has occurred.
I have also tried copying the file to another file, and recreating it there. the effect stays, it simply moves to whichever pivot table it considers the last one created. File size is 279Kb as xlsb, and 336 Kb as xlsm.
Data for the tables is coming from SQL server 2012, and all tables are on the same excel sheet. The tables are updated, but the pivot is not.

推荐答案

尝试类似

Sub refreshTables()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In Worksheets
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next pt
Next ws
End Sub

这篇关于excel不刷新所有数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！