VBA clear just pivot table cache, but leaving pivot table structure

Question

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.

Solution

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.

Comments

[Kazimierz Jawor]

it is quite easy to create PivotTable with VBA. Code is not complicated. Why don't you create PT from scratch with new set of data in PivotCache?

[Przemyslaw Remin]

@KazimierzJawor Everything is easy if you know how:-) It could be accepted solution for me. Can you please provide an answer with an example, not just comment.

[Kazimierz Jawor]

you could find a lot of examples with google, like first or second

[Przemyslaw Remin]

It is clever approach but still from SQL Server side. I would welcome any idea to clear cache from Excel side.

[Przemyslaw Remin]

What variable type is con in the line Var con = ActiveWorkbook.Connections("MyGoodResultConnection")? I mean if you want to dim it.

[Padhraic]

Yes, that should be Dim. I have updated the answer. Plus thanks for the bounty.

[Przemyslaw Remin]

Please review it again. It think it should be first DIM con as vartype and then SET con=ActiveWorkbook.Connections("MyGoodResultConnection")

[Przemyslaw Remin]

I think it should be something like this Dim con As ADODB.Connection or some other sort connection which I do not know and was just asking about it:-)