Excel VBA - RefreshAll 不更新数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35132156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - RefreshAll not updating pivot tables
提问by GavinP
I have a template workbook, which has several data tables connected to sql connections, as well as some pivot tables who's sources are the data brought through.
我有一个模板工作簿，其中有几个数据表连接到 sql 连接，以及一些数据透视表，它们的来源是通过数据带来的。
I was under the impression that ActiveWorkbook.RefreshAllwould update all connections, then update the pivots. That in fact is what happens when I run the refresh all manually. However, when I run the VBA (which is actually in Access, but is correctly referenced etc) it updates the connections but NOT the pivot tables?
我的印象是ActiveWorkbook.RefreshAll会更新所有连接，然后更新支点。这实际上是我手动运行刷新时发生的情况。但是，当我运行 VBA（实际上在 Access 中，但被正确引用等）时，它会更新连接而不是数据透视表？
I've tried DoEventsafter the RefreshAllwhich had no effect.
我试过DoEvents之后RefreshAll没有效果。
Is my only option now to run a For eachthrough all the worksheets, data sources, pivot caches and refresh them that way?
我现在唯一的选择是运行For each所有工作表、数据源、数据透视缓存并以这种方式刷新它们吗？
采纳答案by GavinP
I have solved the issue by using the following
我已经通过使用以下方法解决了这个问题
For Each sht In .Sheets
For Each qt In sht.QueryTables
qt.Refresh
Next qt
For Each lo In sht.ListObjects
lo.QueryTable.Refresh BackgroundQuery:=False
Next lo
For Each pvt In sht.PivotTables
pvt.PivotCache.Refresh
Next pvt
Next sht
回答by Rik Sportel
ActiveWorkbook.RefreshAll does as in matter of fact RefreshAll connections and pivots. However, in your scenario the pivots are probably based on the data you have to refresh first. The pivot will refresh while the data is not loaded yet, hence the unexpected behavior.
ActiveWorkbook.RefreshAll 实际上就像 RefreshAll 连接和枢轴一样。但是，在您的场景中，枢轴可能基于您必须首先刷新的数据。数据尚未加载时，数据透视表将刷新，因此会出现意外行为。
There are multiple solutions for this:
对此有多种解决方案：
Either have the data returned through the connection as a pivotcache so the pivot table will automatically refresh when the data is returned. This way you will not have the data itself stored in a seperate sheet in your workbook either.
Set the "Refresh in Background" property to false for all connections, either in the code or through the UI, then execute as normally. Twice. The second time the pivotcaches will have the updated data and thus refresh as expected. - Edit: I do not recommend this, since you will open the db connection twice, load the data twice, etc. Highly inefficient!
Set the "Refresh in Background"- property to false as mentioned above. After refreshing using Refresh all, loop through your worksheets' pivottable collection to refresh those manually after the data has been loaded as shown below.
要么将通过连接返回的数据作为数据透视缓存，以便数据返回时数据透视表将自动刷新。这样，您也不会将数据本身存储在工作簿的单独工作表中。
在代码中或通过 UI 将所有连接的“后台刷新”属性设置为 false，然后照常执行。两次。第二次数据透视缓存将具有更新的数据并因此按预期刷新。-编辑：我不推荐这个，因为你会打开数据库连接两次，加载数据两次等等。效率非常低！
如上所述，将“在后台刷新”- 属性设置为 false。使用 Refresh all 刷新后，循环遍历工作表的数据透视表集合以在加载数据后手动刷新它们，如下所示。
Code:
代码：
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
Or simply refresh only the pivotcaches (more efficient, especially if multiple tables use the same cache):
或者简单地只刷新pivotcaches（更有效，特别是如果多个表使用相同的缓存）：
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
回答by BuckTurgidson
I have solved the issue by adding a simple 'Calculate' to the code.
我通过在代码中添加一个简单的“计算”解决了这个问题。
Calculate
ActiveWorkbook.RefreshAll
