Deleting "old items" bug with pivot cache
 

Hi,

That's a tricky one, so seat down, take a deep breath and enjoy...

I've been using the following code successfully to delete old items
from the Pivot Tables I use.

Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (Q202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub

....this has never been a problem until I decided to optimise the size
of my excel file by following the method below:
http://groups.google.com/groups?q=au...7th.com&rnum=4
(basicallly this sets all the table to point to the same cache and
then disable cache saving).

The problem I encounter now is as follows:
- excel tries to delete items which are actually existing (i.e. where
pi.recordcount 0 !!!). The Macro crashes and excel too! Even more
irritable is that the error is random (or at least tend to allow me to
perform the operation once and then crashes afterward)

So I have tried the following solutions/workarounds:
- re-enable cache saving (enabling table option "Save data with table
layout")
- no success
- Steping through the code
- works great! (no so practical though, especially if you don't have
access to the code)
- save workbook, Restart Excel2000/win2000
- no success
- included wait state in the code
- no success

Could that be a pivot cache corruption problem?...

Good luck to you all and thanks in advance!

PS: I could send you a sample of my file if you were interested.

Deleting "old items" bug with pivot cache
 

Ok, I got there in the end (took me all afternoon and a lot of
determination)

I have done extensive testing and these are my conclusions...

You will get the "infamous" Err -2147417848 "The object has
disconnected from its clients" if you meet the following conditions:
- Your pivot tables share the same cache
- You have Pivot Charts related to the tables on the same sheet.

To resolve this problem, you may either:
- configure your tables so that they have their own cache (no very
efficient)
- Move your charts on a separate sheet.

Hope this helps some of you!

BR & happy debugging!

Olivier

PS: I'll keep the sample files if some of you are interested

Deleting "old items" bug with pivot cache
 

Further clarification to the above...

An additional criteria to reproduce the bug:
- One of the "old" item to delete is common to the pivot tables/charts
that share the same cache

This explain the reason why the bug originally seemed random.

It would be a good idea to report this bug to M$N. It is not clear to
me how to do this.

BR,

Olivier

Deleting "old items" bug with pivot cache
 

And another one... ;)

You will also encounter this problem if you have some fields grouped
in your pivot table...