Can I add an item to a PivotCache with VBA?

[John Brock]

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

 

[Dave Peterson]

Debra Dalgleish has some instructions here:
http://contextures.com/xlPivot04.html

 

[John Brock]

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.

 

[Qaiser96]

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

 

[Jon Peltier]

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
_______

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.