Excel Pivot OptimizeCache [英] Excel Pivot OptimizeCache

问题描述

这听起来像会让效果更好的东西。我找不到任何关于它实际做什么的文档。我有一些非常大的数据透视表。如果我可以改善我想要的表现。

This sounds like something that would make performance better. I can't find any documentation on what it actually does. I have some really large Pivottables. If I can improve the performance I would like to.

以下 是我的代码

Below  is my code

优化失败。我已经尝试在添加数据后移动它，但没有任何作用。

The optimize fails. I have tried moving it after the data gets added, but nothing works.

删除.OptimizeCache并且代码运行干净。

Removing the .OptimizeCache and the code runs clean.

            Dim objPivotCache As Excel.PivotCache

            

            objPivotCache = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches（）。Create（Excel.XlPivotTableSourceType.xlExternal，System.Reflection.Missing.Value，Excel.XlPivotTableVersionList.xlPivotTableVersion14）

             objPivotCache.OptimizeCache = True

            Dim objPivotCache As Excel.PivotCache
            
            objPivotCache = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlExternal, System.Reflection.Missing.Value, Excel.XlPivotTableVersionList.xlPivotTableVersion14)
            objPivotCache.OptimizeCache = True

          使用objPivotCache

                .SavePassword = False

                " .BackgroundQuery =真



              &NBSP ; .Recordset = RSTB



                " 错误恢复接下来是
                .CreatePivotTable（TableDestination：= os，TableName：= tqtname）

           With objPivotCache
                .SavePassword = False
                '.BackgroundQuery = True

                .Recordset = rstb

                '  On Error Resume Next
                .CreatePivotTable(TableDestination:=os, TableName:=tqtname)

推荐答案

嗨AuroraMan1，

Hi AuroraMan1,

根据以下链接，缓存优化会导致其他查询并降低数据透视表报告的初始性能。

Based on the link below, Cache optimization results in additional queries and degrades initial performance of the PivotTable report.

#PivotCache.OptimizeCache property

https：// msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pivotcache.optimizecache.aspx?f=255&MSPPError=-2147217396

# PivotCache.OptimizeCache property
https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pivotcache.optimizecache.aspx?f=255&MSPPError=-2147217396

"优化失败"是什么意思？你有没有错误？如果您手动创建数据透视表，您是否得到相同的问题？如果没有，我建议您尝试记录您手动操作的步骤，并检查录制的代码。

What do you mean by "optimize fails"? Did you get any error? If you manually create Pivot Table, did you get the same issue? If not, I suggest you try to record steps which you manually operate, and check the recorded code.

最好的问候，

Best Regards,

Edward

这篇关于Excel Pivot OptimizeCache的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！