excel - RefreshAll 不更新数据透视表

Question

我有一个模板工作簿，其中有几个连接到sql连接的数据表，以及一些数据透视表，其来源是通过带来的数据。

我的印象是ActiveWorkbook.RefreshAll会更新所有连接，然后更新数据透视表。事实上，这就是我手动运行刷新时发生的情况。但是，当我运行 VBA(实际上在 Access 中，但被正确引用等)时，它会更新连接而不是数据透视表？

我在 RefreshAll 之后尝试了 DoEvents，但没有效果。

我现在唯一的选择是通过所有工作表、数据源、数据透视缓存运行For every并以这种方式刷新它们吗？

Answer 1

ActiveWorkbook.RefreshAll 实际上是 RefreshAll 连接和枢轴。但是，在您的场景中，数据透视表可能基于您必须首先刷新的数据。当数据尚未加载时，数据透视表将刷新，因此会出现意外的行为。

对此有多种解决方案:

• 要么将通过连接返回的数据作为数据透视缓存，以便数据返回时数据透视表将自动刷新。这样，您也不会将数据本身存储在工作簿的单独工作表中。

• 在代码中或通过 UI 将所有连接的“后台刷新”属性设置为 false，然后正常执行。 两次。第二次，数据透视缓存将具有更新的数据，从而按预期刷新。 - 编辑:我不建议这样做，因为您将打开数据库连接两次，加载数据两次等等。效率非常低!

• 如上所述，将“后台刷新”属性设置为 false。使用“全部刷新”进行刷新后，循环遍历工作表的数据透视表集合，以在加载数据后手动刷新这些集合，如下所示。

代码:

Sub test()

Dim ws as Worksheet
Dim pt as PivotTable

ActiveWorkbook.RefreshAll 'make sure the refresh in bg property is false for all connections

For each ws in ActiveWorkbook.Worksheets
    For each pt in ws.pivottables
        pt.RefreshTable
    Next pt
Next ws

End Sub

或者仅刷新数据透视缓存(效率更高，特别是在多个表使用相同缓存的情况下):

Sub test()
Dim pc as PivotCache

ActiveWorkbook.RefreshAll 'make sure the refresh in bg property is false for all connections

For each pc in ActiveWorkbook.PivotCaches
    pc.Refresh
Next pc

End Sub