防止Excel刷新数据透视表，直到VBA被告知 [英] Prevent Excel from refreshing Pivot Table until told to do so by VBA

问题描述

我有一个Excel模板（.xlt），其中包含报告数据的工作表，以及包含数据透视表的4x工作表。

当我们的报表系统创建报表时将模板应用于其中，由数据透视表引用的一些字段不存在，因为运行宏作为报表系统过程的一部分。

但是，如果这些字段不存在，Excel会抛出错误（访问组件属性/方法：REFRESH时发生错误。参考无效）。为了尝试并防止这种情况，在打开文件时，我已经在刷新数据的所有数据透视表上取消设置选项。

我知道我可以使用VBA刷新数据透视表，但是当打开文档列表时，是否有一种方法可以防止数据透视表刷新该文件夹，而不是在创建新字段之后通过VBA刷新？感谢。

 功能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通过）.PivotTables（PivotTable3）。PivotCache.Refresh 
 ActiveWorkbook.Sheets（DJG Marketing  -  Client List by）.PivotTables（PivotTable4）。PivotCache.Refresh 
结束函数
  

解决方案

这里是您需要的：

 '禁用自动计算
 Application.Calculation = xlCalculationManual 
'在这里执行常规操作
'强制计算
 Application.Calculate 
'然后记得运行自动计算
 Application.Calculation = xlCalculationAutomatic 
  

取自：
http://www.zerrtech.com/content/stop-vba-automatic-calculation- use-applicationcalculation-manual

I have an Excel Template (.xlt) that contains a Worksheet for report data, and 4x Worksheets with Pivot Tables.

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.

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.

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

解决方案

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

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