Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Can I add an item to a PivotCache with VBA?
If I create a pivot table using a database which contains a particular
value, let's say "3", and then I change all the 3s to something else, the pivot table remembers the missing value. I.e., if I check the "Show items with no data" checkbox for that field, the pivot table will contain a column called "3" even though there are no 3s any more. What's more, that column will continue to appear even after I refresh the pivot table. In fact, the only way I can make that column go away permanently is by calling PivotItems("3").Delete() from VBA. (Is there a way to do this manually?) However if I try to add a PivotItem to the table using VBA I can do it, but the added column disappears the minute I do a refresh. The best I can figure is that adding a PivotItem to the table doesn't touch the cache, while adding data to the database does, even after that data has been deleted. So is there any way I can tell the pivot cache (not the pivot table!) to add a new item? Deleting the item from the table seems to affect the cache, but there doesn't seem to be any obvious way to add items. (I suppose I could add rows containing the desired values to the database, refresh the pivot table, and then remove the added rows, but that seems really hackish, and I'd much rather do it directly). -- John Brock |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Can I add an item to a PivotCache with VBA?
Debra Dalgleish has some instructions he
http://contextures.com/xlPivot04.html John Brock wrote: If I create a pivot table using a database which contains a particular value, let's say "3", and then I change all the 3s to something else, the pivot table remembers the missing value. I.e., if I check the "Show items with no data" checkbox for that field, the pivot table will contain a column called "3" even though there are no 3s any more. What's more, that column will continue to appear even after I refresh the pivot table. In fact, the only way I can make that column go away permanently is by calling PivotItems("3").Delete() from VBA. (Is there a way to do this manually?) However if I try to add a PivotItem to the table using VBA I can do it, but the added column disappears the minute I do a refresh. The best I can figure is that adding a PivotItem to the table doesn't touch the cache, while adding data to the database does, even after that data has been deleted. So is there any way I can tell the pivot cache (not the pivot table!) to add a new item? Deleting the item from the table seems to affect the cache, but there doesn't seem to be any obvious way to add items. (I suppose I could add rows containing the desired values to the database, refresh the pivot table, and then remove the added rows, but that seems really hackish, and I'd much rather do it directly). -- John Brock -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Can I add an item to a PivotCache with VBA?
Thank you for a useful page. Alas though, it only explains how to
clear old items from a pivot cache. I want to add new "old" items, without the runaround of adding and then deleting records from the database. In article , Dave Peterson wrote: Debra Dalgleish has some instructions he http://contextures.com/xlPivot04.html John Brock wrote: If I create a pivot table using a database which contains a particular value, let's say "3", and then I change all the 3s to something else, the pivot table remembers the missing value. I.e., if I check the "Show items with no data" checkbox for that field, the pivot table will contain a column called "3" even though there are no 3s any more. What's more, that column will continue to appear even after I refresh the pivot table. In fact, the only way I can make that column go away permanently is by calling PivotItems("3").Delete() from VBA. (Is there a way to do this manually?) However if I try to add a PivotItem to the table using VBA I can do it, but the added column disappears the minute I do a refresh. The best I can figure is that adding a PivotItem to the table doesn't touch the cache, while adding data to the database does, even after that data has been deleted. So is there any way I can tell the pivot cache (not the pivot table!) to add a new item? Deleting the item from the table seems to affect the cache, but there doesn't seem to be any obvious way to add items. (I suppose I could add rows containing the desired values to the database, refresh the pivot table, and then remove the added rows, but that seems really hackish, and I'd much rather do it directly). -- John Brock -- Dave Peterson -- John Brock |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Can I add an item to a PivotCache with VBA?
Click on the Pivot Table and then do Right click and select the refresh
option. This would help the pivot table to take the effect of the changes you have made to the database.... -- Qaiser "John Brock" wrote: If I create a pivot table using a database which contains a particular value, let's say "3", and then I change all the 3s to something else, the pivot table remembers the missing value. I.e., if I check the "Show items with no data" checkbox for that field, the pivot table will contain a column called "3" even though there are no 3s any more. What's more, that column will continue to appear even after I refresh the pivot table. In fact, the only way I can make that column go away permanently is by calling PivotItems("3").Delete() from VBA. (Is there a way to do this manually?) However if I try to add a PivotItem to the table using VBA I can do it, but the added column disappears the minute I do a refresh. The best I can figure is that adding a PivotItem to the table doesn't touch the cache, while adding data to the database does, even after that data has been deleted. So is there any way I can tell the pivot cache (not the pivot table!) to add a new item? Deleting the item from the table seems to affect the cache, but there doesn't seem to be any obvious way to add items. (I suppose I could add rows containing the desired values to the database, refresh the pivot table, and then remove the added rows, but that seems really hackish, and I'd much rather do it directly). -- John Brock |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Can I add an item to a PivotCache with VBA?
A pivot table doesn't allow display of data that doesn't (or didn't) come
from the source database. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John Brock" wrote in message ... Thank you for a useful page. Alas though, it only explains how to clear old items from a pivot cache. I want to add new "old" items, without the runaround of adding and then deleting records from the database. In article , Dave Peterson wrote: Debra Dalgleish has some instructions he http://contextures.com/xlPivot04.html John Brock wrote: If I create a pivot table using a database which contains a particular value, let's say "3", and then I change all the 3s to something else, the pivot table remembers the missing value. I.e., if I check the "Show items with no data" checkbox for that field, the pivot table will contain a column called "3" even though there are no 3s any more. What's more, that column will continue to appear even after I refresh the pivot table. In fact, the only way I can make that column go away permanently is by calling PivotItems("3").Delete() from VBA. (Is there a way to do this manually?) However if I try to add a PivotItem to the table using VBA I can do it, but the added column disappears the minute I do a refresh. The best I can figure is that adding a PivotItem to the table doesn't touch the cache, while adding data to the database does, even after that data has been deleted. So is there any way I can tell the pivot cache (not the pivot table!) to add a new item? Deleting the item from the table seems to affect the cache, but there doesn't seem to be any obvious way to add items. (I suppose I could add rows containing the desired values to the database, refresh the pivot table, and then remove the added rows, but that seems really hackish, and I'd much rather do it directly). -- John Brock -- Dave Peterson -- John Brock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to pair each item on one list to each item on another list | Excel Worksheet Functions | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
Remove specific Pivotcache fields | Excel Discussion (Misc queries) | |||
Where's my XML item? | Excel Discussion (Misc queries) | |||
Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions |