Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot cache when pivot table copied
Hello,
I have a worksheet that contains a pivot table which uses an external data source (SQL). I've written a script to update the SQL statement in the pivot cache so the user can simply change some query criteria on the worksheet and update the pivot table (specifically, the user can enter a name in a cell and the pivot table displays work data pulled from MS Project server). I would like the user to be able to copy the worksheet, change the name and update the pivot table (ex.: so he can create one worksheet per team member). When I make a copuy of the worksheet the script doesn't work. I've found that the pivot table initialy has the same pivot cache as the original (same cache index). The only way I have found to get a new pivot cache is to manualy go and change the data source for the pivot table...this creates a new pivot cache and the script works fine after that. Is there anyway to do this programatically? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot cache when pivot table copied
Ok...found this workaround:
Dim ptProjects As PivotTable Dim NewPC As PivotCache Dim NewPT As PivotTable '*** Get a refrence to the current pivot table on the new worksheet Set ptProjects = ActiveSheet.PivotTables("ResProjects") '*** Create temporary pivot table with new pivot cache in out of the way location Set NewPC = ActiveWorkbook.PivotCaches.Add(xlExternal) NewPC.Connection = "ODBC connection string goes here" NewPC.CommandText = "SQL statements" NewPC.CommandType = xlCmdSql Set NewPT = NewPC.CreatePivotTable(Range("Z1"), "Temp") NewPC.Refresh '*** Link current pivot table to new pivot cache associated to temp pivot table ptProjects.CacheIndex = NewPT.CacheIndex '*** Delete temporary pivot table Columns("Z:AD").Select Selection.Delete Shift:=xlToLeft Range("A1").Select "Michael Glenn" wrote: Hello, I have a worksheet that contains a pivot table which uses an external data source (SQL). I've written a script to update the SQL statement in the pivot cache so the user can simply change some query criteria on the worksheet and update the pivot table (specifically, the user can enter a name in a cell and the pivot table displays work data pulled from MS Project server). I would like the user to be able to copy the worksheet, change the name and update the pivot table (ex.: so he can create one worksheet per team member). When I make a copuy of the worksheet the script doesn't work. I've found that the pivot table initialy has the same pivot cache as the original (same cache index). The only way I have found to get a new pivot cache is to manualy go and change the data source for the pivot table...this creates a new pivot cache and the script works fine after that. Is there anyway to do this programatically? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table (column/row) label cache reset | Excel Discussion (Misc queries) | |||
How to Clear a Pivot Table Cache | Excel Discussion (Misc queries) | |||
Excel Pivot Table cache | Excel Discussion (Misc queries) | |||
multiple pivot tables from 1 pivot cache | Excel Programming | |||
Can't set CommandText if pivot table copied | Excel Programming |