防止 Excel 刷新数据透视表，直到被 VBA 告知这样做
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12643025/
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
Prevent Excel from refreshing Pivot Table until told to do so by VBA
提问by David Gard
I have an Excel Template (.xlt) that contains a Worksheet for report data, and 4x Worksheets with Pivot Tables.
我有一个 Excel 模板 (.xlt)，其中包含用于报告数据的工作表和带有数据透视表的 4x 工作表。
When our reporting system creates a report and applys the template to it, some of the fields referenced by the Pivot Tables don't exist, as a Macro is run to create them as part of the process of the reporting system.
当我们的报告系统创建一个报告并将模板应用到它时，数据透视表引用的一些字段不存在，因为运行宏来创建它们作为报告系统过程的一部分。
However, bescuse these fields do not exist, Excel is throwing up an error (Error occurred while accessing component property/method: REFRESH. Reference is not valid.). To try and prevent this, I have unset the option on all Pivot Tables for Refresh data when opening the file.
但是，由于这些字段不存在，Excel 抛出错误（访问组件属性/方法时发生错误：REFRESH。引用无效。）。为了防止这种情况发生，我在所有数据透视表上取消了Refresh data when opening the file.
I know that I can refresh the Pivot Tables using VBA, but is there a way to prevent the Pivot Tables refreshing themsleves when the documnet is opened, and instead refresh them through VBA, after the new fields have been created? Thanks.
我知道我可以使用 VBA 刷新数据透视表，但是有没有办法防止数据透视表在文档网打开时刷新它们，而是在创建新字段后通过 VBA 刷新它们？谢谢。
Function UpdatePivots()
ActiveWorkbook.Sheets("DJG Marketing - Client List by ").PivotTables("PivotTable1").PivotCache.Refresh
ActiveWorkbook.Sheets("DJG Marketing - Client List by ").PivotTables("PivotTable2").PivotCache.Refresh
ActiveWorkbook.Sheets("DJG Marketing - Client List by ").PivotTables("PivotTable3").PivotCache.Refresh
ActiveWorkbook.Sheets("DJG Marketing - Client List by ").PivotTables("PivotTable4").PivotCache.Refresh
End Function
回答by l--''''''---------''''''''''''
here's what you need:
这是你需要的：
' Disable automatic calculation
Application.Calculation = xlCalculationManual
' do regular operation here
' Force a calculation
Application.Calculate
' Then remember to run automatic calculations back on
Application.Calculation = xlCalculationAutomatic
taken from: http://www.zerrtech.com/content/stop-vba-automatic-calculation-using-applicationcalculation-manual
取自：http: //www.zerrtech.com/content/stop-vba-automatic-calculation-using-applicationcalculation-manual
