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
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