VBA仅清除数据透视表缓存，但保留数据透视表结构 [英] VBA clear just pivot table cache, but leaving pivot table structure

问题描述

如何使用VBA清除数据透视表缓存，但不破坏数据透视表结构?我的数据透视表已连接到外部数据源. SQL源确定哪个用户应查看数据的哪一部分.刷新表时，源将填充表.我想保存Excel文件并使用干净的数据透视表分发它(里面没有数据).

因此，我想得到以下确切信息:

我已经围绕此代码进行了实验，但没有成功.在VBA中没有像PivotCaches.Clear这样的东西.

Sub PT_cache_clear()
    For Each pc In ActiveWorkbook.PivotCaches

        pc.Clear

    Next pc
End Sub

我发现的唯一好的解决方案是用有权访问SQL Server源但不允许查看任何数据记录的用户刷新表.

想法:

ActiveSheet.PivotTables("PivotTable1").SaveData = False

似乎不会导致预期的结果.

解决方案

我的方法是使用查询刷新，该查询将返回表结构但记录为0.因此，如果从视图中进行选择，例如:

select top 0 * 
from vw_MyPivotData

如果使用存储过程，则可以发送确保不返回任何记录的参数，例如您知道数据中不存在的过滤器，或设计用于不返回记录的特殊参数. /p>

How can I clear pivot table cache with VBA, but not destroy pivot table structure? My pivot table is connected to external data source. The SQL source determines which user should see which portion of the data. The source populates the table as the table is refreshed. I want to save the Excel file and distribute it with clean pivot table (no data inside).

As a result I want to get exactly this:

I have experimented around this code with no success. There is no such thing like PivotCaches.Clear in VBA.

Sub PT_cache_clear()
    For Each pc In ActiveWorkbook.PivotCaches

        pc.Clear

    Next pc
End Sub

The only good solution I found is to refresh the table with a user which has access to SQL server source but is not allowed to see any single record of the data.

The idea:

ActiveSheet.PivotTables("PivotTable1").SaveData = False

seems not to lead to desired results.

解决方案

The way I do it is to refresh with a query that will return the table structure but with 0 records. So if selecting from a view something like:

select top 0 * 
from vw_MyPivotData

If using a stored procedure, you can send a parameter that ensures that no records will be returned such as a filter that you know doesn't exist in the data or a special parameter devised for the purpose of returning no records.

这篇关于VBA仅清除数据透视表缓存，但保留数据透视表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！