PDA

View Full Version : Cleaning out pivot table and then using same pivot vache



Lionheart
01-07-2011, 08:15 AM
I have created a pivot cache and pivot table using external data from a stored proc. What I would like to do is clear all data from the pivot table but using the same pivot cache reinsert the fields.

The following is the code for clearing the pivot table

For index = shtMain.PivotTables.Count To 1 Step -1
If Application.Version = "12.0" Then shtMain.PivotTables(index).ClearTable
Next index


For initally creating the cache I have used

Dim oPivotCache As PivotCache
Set oPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)

With oPivotCache
.Connection = SQL_CONNECTION_STRING
.CommandType = xlCmdSql
.CommandText = GetPivotDataCmd()
Set oPivotTable = .CreatePivotTable( _
TableDestination:=shtMain.Cells(10, 3), _
TableName:=PIVOTTABLE_1, _
DefaultVersion:=xlPivotTableVersion10)
End With


What I need is to have a command something like

Dim oPivotCache As PivotCache
Set oPivotCache = ActiveWorkbook.PivotCaches WHAT GOES HERE?

So that it uses the same data that is stored in the original cache?

Thx

LH

Sean.DiSanti
01-10-2011, 12:09 PM
so you want to use the same data, and the same fields? maybe you just RefreshTable ?

Lionheart
01-11-2011, 04:55 AM
Thanks. I had the code I needed in the end, just needed to add a sheet reference for the cache. Thought that as it was the only sheet it would be okay, but not the case.

LH