Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable question
I found a way to refresh data in pivot table by changing its query (PivotCache.CommandText). The problem is that, even though data gets refreshed fine, the dimension members remain the same. How do I go about refreshing everything? The "Refresh Data" toolbar button refreshes all. BTW, I also noticed that, if a dimension is already used on one of the pivot pages, its members will never refresh until it gets removed from pivot page and the data got refreshed again. Finally, dimension members do refresh with new members (if query changed so), but old members that no longer should show are still there. Any ideas? TIA lc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable question
There's code on the following page for removing old items from a
dropdown. It's not tested on an OLAP cube, but may help: http://www.contextures.com/xlPivot04.html lc wrote: I found a way to refresh data in pivot table by changing its query (PivotCache.CommandText). The problem is that, even though data gets refreshed fine, the dimension members remain the same. How do I go about refreshing everything? The "Refresh Data" toolbar button refreshes all. BTW, I also noticed that, if a dimension is already used on one of the pivot pages, its members will never refresh until it gets removed from pivot page and the data got refreshed again. Finally, dimension members do refresh with new members (if query changed so), but old members that no longer should show are still there. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable question
"Debra Dalgleish" wrote in message ... There's code on the following page for removing old items from a dropdown. It's not tested on an OLAP cube, but may help: Thanks Debra. I stumbled across these examples and tried both of them. The MissingItemsList simply doesn't work with 2003. pt.PivotCache.MissingItemsList = xlMissingItemsNone pt.PivotCache.CommandText = ..... but nothing happens. It appears (at least with 2003) that you must call PivotTable.RefreshTable or PivotTable.PivotCache.Refresh. That updates everything but it also re-queries the datasource. Problem is that the query already ran when I assigned a SQL statement to CommandText. PivotTabe.ManualUpdate doesn't seem to have any impact. With For loop, items do disappear but its a long process and if one of the on-screen dimensions contains a number of items (like 300), the screen flickers with each Item delete. Is there a way to lock screen for updates? Thanks a lot. lc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable question
I'm a bit confused.Do you just want:
ActiveSheet.PivotTables("PivotTableNameInHere").Re freshTable This is from a recorded macro Or: ThisWorkbook.RefreshAll This will refresh all pivots in the workbook. Regards, Jason. Debra Dalgleish wrote in message ... There's code on the following page for removing old items from a dropdown. It's not tested on an OLAP cube, but may help: http://www.contextures.com/xlPivot04.html lc wrote: I found a way to refresh data in pivot table by changing its query (PivotCache.CommandText). The problem is that, even though data gets refreshed fine, the dimension members remain the same. How do I go about refreshing everything? The "Refresh Data" toolbar button refreshes all. BTW, I also noticed that, if a dimension is already used on one of the pivot pages, its members will never refresh until it gets removed from pivot page and the data got refreshed again. Finally, dimension members do refresh with new members (if query changed so), but old members that no longer should show are still there. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable question
"jason" wrote in message om... I'm a bit confused.Do you just want: ActiveSheet.PivotTables("PivotTableNameInHere").Re freshTable This is from a recorded macro Or: ThisWorkbook.RefreshAll I change the query in my procedure and load it into PivotTable.PivotCache.CommandText to execute it. Just by doing this, the query fires and data gets refreshed. However, what doesn't get refreshed are the items in the dimensions fields. In order to remove them I would either 1) loop through PivotFields and PivotField.Items and delete the appropriate ones. The problem with this solution is that each delete causes the screen to refresh with a lot of flicker. Besides, with larger dimensions, this can literally take ages. 2) call PivotTable.RefreshTable or PivotTable.PivotCache.Refresh. Either of these two cause the query to fire again where, in fact, the query already ran when CommandText was assigned. Once again, MissingItemsList has no effect in 2003 - not that I can see, at least. Thanks. lc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable question
In case anyone else runs into the same catch 22 game: I took a longer route and am deleting PivotItems myself. The screen flicker is gone with a call to Application.ScreenUpdating. lc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable question | Excel Discussion (Misc queries) | |||
PivotTable Question | Excel Worksheet Functions | |||
PivotTable Question | Excel Discussion (Misc queries) | |||
PivotTable Question | Excel Worksheet Functions | |||
PivotTable Question | Excel Discussion (Misc queries) |