VBA: Pivot Cache Help

[Scott]

I was hoping someone could help me with updating pivot tables in VBA.

Currently I have a workbook with 10 pivot tables in it. I have
another file that calculates a bunch of data. Then, using VBA, I
create a new sheet in the Pivot Table workbook that contains the flat
file. I then loop through all the pivot tables and use
PivotCache.Create for each pivot basing the cache on the flat file
data.
-------------------------------------------------------------------------------------------------------------------

The code is as follows:

' Update all of the pivot tables with the new flat file data
For Each ws In outputWB.Worksheets
For Each pt In ws.PivotTables
pt.ChangePivotCache
outputWB.PivotCaches.Create(xlDatabase, pivotSheet.Range(Cells(1, 1),
pivotSheet.Cells(pivotEndRow, pivotEndCol)))
pt.RefreshTable
pt.SaveData = True
Next pt
Next ws

-------------------------------------------------------------------------------------------------------------------

This makes the file size very large. What I would like to do is
create a single Pivot Cache, and then loop through each of the pivot
tables and update their cacheindex to equal the cache I created. I
can't seem to get this to work.

I would like to do something like:
-------------------------------------------------------------------------------------------------------------------

Dim pc as PivotCache

set pc = outputWB.PivotCaches.Create(xlDatabase,
pivotSheet.Range(Cells(1, 1), pivotSheet.Cells(pivotEndRow,
pivotEndCol)))

For Each ws In outputWB.Worksheets
For Each pt In ws.PivotTables
pt.CacheIndex = pc.index
pt.RefreshTable
pt.SaveData = True
Next pt
Next ws


-------------------------------------------------------------------------------------------------------------------

Does anybody have any suggestions as to how to modify the code such
that I am able to create a single PivotCache and have each of the
pivot tables use that single pivot cache?

Thanks!

 

[skip_borland]

Does this help?

Set one pivot table's data source to the correct dataset or db output. Select the table, right-click and get the table name (by default it will be something like "PivotTable1").

Copy the following code an place it in a code module, placing the "XXXXX" with the name identified earlier.

This will identify the cache number of the pivot table you previously hooked up to your dataset and set all the other pivot tables on the sheet to the same cache. Incidentally, once they are all set to the same single cache, if there are no other caches present, it will return to index 1.

Skip


Sub ResetPivotCache()

Dim wks As Worksheet
Dim pt As PivotTable
Dim pc As PivotCache
Dim rng As Range
Set wks = ActiveSheet

Set pt = wks.PivotTables("XXXX")
i = pt.CacheIndex
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
pt.RefreshTable

For Each pt In wks.PivotTables

If pt.CacheIndex <> i Then pt.CacheIndex = i
pt.RefreshTable​

Next pt

Set wks = Nothing
Set pt = Nothing
Set pc = Nothing
​

End Sub