Excel Pivot Table - Clear cache memory 1

[ItIsHardToProgram]

Hello everyone,

I have mentioned this earlier in another post, where we found a fix that seemed to work, but the file decided that it started RECREATING ghosts again, in my data, and my clear cache method dosnt seem to be removing them.

Is there a way to COMPLETLY rewrite cache, before every procedure.

I have a print macro that prints data, thats what seems to be creating the ghosts, there is probably a reason for that, a reason that I don't know.

Please help me this issue is really getting on my wrong side.

Here is the macro to clear cache.

Sub CleanMyPivots()
'SkipVought/2007 Apr 30/817-280-5438
'--------------------------------------------------
'cleans up pivot tables having OLD/UNUSED data hanging around.
'--------------------------------------------------
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
       pc.MissingItemsLimit = xlMissingItemsNone
       pc.Refresh
    Next
End Sub

Here is what the print macro does:

Sub ImpressionQuebec()

CleanMyPivots

On Error Resume Next
Dim pvi As PivotItem
Dim PviName() As String
Dim NamePVI


Call SortRes(PviName)


If ActiveWorkbook.Sheets("États").Range("$B$2:$B$2").Value = "Fonds I" Then
    Application.ScreenUpdating = False
    With Sheets("États")
            With .PivotTables("EtatsFinancier")
                .PivotFields("Résidence").CurrentPage = "(Tous)"
                .PivotFields("Région").CurrentPage = "(Tous)"
                .PivotFields("VPR").CurrentPage = "Renée"
                Sheets("ÉtatsRésultat").PrintOut Copies:=1, Collate:=True
                
                For Each NamePVI In PviName
                    .PivotFields("Résidence").CurrentPage = NamePVI
                    
                    Select Case NamePVI
                        Case "StGeorges", "Atrium", "Saguenay", "Jonquiere", "Cascades", _
                        "RiveSud", "Archer", "Estrie"
                            Sheets("ÉtatsRésultat").PrintOut Copies:=1, Collate:=True
                            If NamePVI = "Archer" Then Exit Sub
                            'MsgBox (NamePVI)
                        Case Else
                            'Sheets("ÉtatsRésultat").PrintOut Copies:=1, Collate:=True
                            'MsgBox (pvi.Value)
                    End Select
            Next
        End With
    End With
    Application.ScreenUpdating = True
End If

Cleanmypivots
End sub

If any one could help me with this issue I would be very happy, ghost that are created replace data with otehr data (only in the pivot table) and they also create data that doesnt exist, for example, pembroke creates pembroke2, but there is no pembroke2.

ALSO there is a weird parcel of data that represents nothing and appears as a blank.

The data is not reflected in any category of my pivot table.

Im thinking this is corruption and im hoping not.

Can you find me a fix, beside redo the pivot table everytime???

Thank you

Julien,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

So the error it gives me is a 438 error, method not managed by the specific item.


But back to my problem, the only fix is a manual one by redoing the whole pivot table?

=/ thats very unconvenient, will ms come with a fix in next updates?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

I have seen this happen when a field NAME or ITEM is overtyped with something else... "

You mean changing a WHOLE batch of source data, for example if I was to turn "Rideau" into "Charmander"

Because this was never done, only thing that was done is there was "RideauAG" that was added, wich ive changed to AGRideau after my problem appear, to see if that was nto the problem....

Sigh, this problem is complicated.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Sorry. Without seeing the actual problem, I have no more ideas. No, you should not have to redo the entire PT unless it has become somehow corrupted, and I have never seen that.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

You mean changing a WHOLE batch of source data, for example if I was to turn "Rideau" into "Charmander"

No - by overtyping the field name or item description in the pivot table itself - NOT in the source data - that would be fine!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ItIsHardToProgram]

I would attach the file, without any numbers, but I don't want to subscribe or purchase for that ftp server.

Maybe I could send it via something else?

Any way, for the considerate effort you put in this thread, have a star.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

than xlbo, we never actualy, well I any way, retyped anything directly in the pivot table.

so that clears out that behavior.

Would you consider it being corruption, considering my printmacro scrolls the data fast...

Im afraid we will have to redo pivot tables until we find a permanent fix.

:/

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[xlbo]

To be honest, I was hoping that you or Skip would find the fix cause I have a user here that has a similar issue - ghost data that used to be in the pivot table - isn;t now but won;t get cleared out using the missingitemslimit = xlmissingitemsnone fix.....given what has been said, I can only assume that yes, there is some corruption somewhere - which means the file I'm looking at is probably corrupt and will need rebuilding!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

There goes BG behaving badly again!!!

He's not sleepless in Seattle!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

This is becoming a very very very problematic issue.

We are issuing reports to head office and part of the time the numbers are wrong because data is colluding, I know were not able to come with a fix, but this is still a very active problem.

I am presently considering redesigning the whole file....

Thanks for your help...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Consider using MS Query.

You can use to generate a list of unique values.

You can use to return a resultset for a criteria equal to a value in the list, in a loop.

I use this technique more often than I use a PivotTable with Page Fields.

faq68-5829.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

As I already read, but this add-on is not installed on the system.

=/

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

I had new intel that might give hope to a fix.

I was trying to capture errors and I found out that through my routine, it gives me a error number 1004

"impossible to define the _Default class of the pivot item"

Could this have anything to do with my problem

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Welll it's important to know what statement the error occured on, and what properties and value are assigned at the time.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

I found exactly what was the problem with the error msg, the first value of my sorting was always blank because this was the code
i = i + 1
list(i) = (my reference)

Ive managed it so that i don't get any errors, unfortunatly this has not fix my problem....

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[drektegek]

I have the same problem with my workbook. It contains 32 pivot tables based on 9 different CSV-datafiles. Searching the net only gave answers for getting rid of the ghost pivot items. But I couldn't find any solution for the problem with automatically renamed pivot items.

But maybe I've found a workaround :-D!!!

When examinating the cache I've found that the problem wasn't in the cache memory. Doubleclicking on the pivot total gave the right records with the right values. So the the values are only wrong in the pivot table. To refresh the list with pivot items you simply change the fieldtype of the pivot field (i've changed it from standard to numeric). Worked fine for me :-D. I'm still testing it and when it's successfull I'll write a code for this workaround.

Maybe this can help further....

 

[SkipVought]

drektegek,

YOU know what you are referring to, but no one else does.

Perhaps you ought to post a new thread, stating the specifics of what you did and why. Use examples of source data and pivottable results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Searching the net only gave answers for getting rid of the ghost pivot items."

Such as?

"automatically renamed pivot items."

Im talking about data collusion, not pivot item renaming, that would barely be a problem.

"you simply change the fieldtype of the pivot field (i've changed it from standard to numeric)."

Wich pivot field, when would yo do this, How in the world can that fix the problem...

Ive tried chaning the format of the pivot fields after the corruption and no joy, might want to elaborate?

Cordialy,

Julien Roy


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ToZo]

Hi,
I have a similar problem, reproducable.

It seems to be connected to a memory leak IMHO.
My macro changes the pivot page fields of all pivot tables, then copies several sheets (with pivot tables) into a new workbook, saves this under a new name and then prints it.

When this is executed as a loop with only a 1 or 2 rounds, no problem. I tested and changed several times.

But when I run it in e.g. 10 loops (i.e. the page field is changed to 10 different values) I can corrupt the workbook.
For a page field now the pivot table row/column values definitely are not the ones belonging to it, instead actually belonging to another page field value.
This can not be fixed anymore, no cleared caches, no data refresh (data is in another workbook).

I had this already 2-3 times before, each time had re-created all pivot tables, pivot charts. Painful.

BTW: I am using Excel 2007, which ought to be able to handle around 60000 records without problems, more then Excel 2003.

Anyone with an idea how to motivate Excel to clear everything in memory, would be highly welcome

Toni