none
Pivot Table RRS feed

  • Question

  • I've done extensive research on this problem and it seems pervasive: 
    Excel Pivot tables will fail to work from time-to-time due to some sort of corruption. 
    Failures include: 
    1) not changing data when the pivot table's Page-level filter value is reset via VBA.
    2) not sorting data based on a VBA-based sorting option

    I've looked at changing the pivotcache using code from the Contextures website...to no avail.
    Since there are dozens of pivot tables in the complex workbook I am supporting, I'd like an automated solution to this problem as each pivot table would take hours to rebuild properly from scratch.
    More importantly: this could happen in the future as well !!

    Does anyone have a solution to this ? I guess one option would be to gather all of the important properties of the pivot table and each of the pivot fields and then create a new pivot table from those properties into a new worksheet.

    Another thought: Can I convert the XLSX file to zip format and then delete the pivot cache part for the corrupted pivot tables ? Would that work or cause other problems ?
    Note: Excel's Open and Repair does nothing to resolve this issue.
    • Edited by Syswizard Monday, May 8, 2017 4:18 PM
    Monday, May 8, 2017 4:18 PM

Answers

  • I finally discovered the problem.

    It's a long, long-standing problem with assigning page-level fields in a PivotTable from VBA:

    oShtReview.PivotTables("pvTrades").PivotFields("ClientName").CurrentPage = [ClientName].Value

    If [ClientName].Value is not in the Pivot Table page-level Filter items, Excel will generate it !!
    That is fatal....as now it's internal index is "off".

    The solution....is very simple:
    Move the page-level field to row-level.....and then Sort it.
    Then drag it back to the page-level.

    A better solution: Excel should have a property to insure new entries cannot be inserted if the page-level filter is assigned a value not in the item list. Right now one would have to iterate the entire page-level filter's item list before making the assignment. That would be prohibitively slow.

    Friday, May 12, 2017 10:45 PM

All replies

  • Hi Syswizard,

    you had mentioned that you are working with large amount of data.

    this can be the reason for undesirable output.

    you had mentioned that you are using VBA code.

    did you test that VBA code works correctly with large amount of data?

    it is possible that it work correctly with small data but give different output with large data.

    you need to provide some example of corruption and also need to provide a steps to reproduce the issue.

    so that we can try to test that on our side.

    if we can confirm that this corruption is done by Excel Application then we can submit feedback to Excel user voice.

    if this corruption is done by your VBA code then you need to correct your code.

    you had mentioned that,"Does anyone have a solution to this ? I guess one option would be to gather all of the important properties of the pivot table and each of the pivot fields and then create a new pivot table from those properties into a new worksheet."

    but if it is corrupted then you are not able to access properties and data accurately.

    other idea you had mentioned that,"Another thought: Can I convert the XLSX file to zip format and then delete the pivot cache part for the corrupted pivot tables ? Would that work or cause other problems ?
    Note: Excel's Open and Repair does nothing to resolve this issue."

    I don't suggest you to follow this approach because the file contains large amount of data. how you will find the corrupted data from that. when you open the zip file you will see many different files. it is not possible to identify the data from there. so if you try it then also the whole file will be corrupted by you and you will not able to use it further.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 9, 2017 8:03 AM
    Moderator
  • When I attempted to change the Pivotcache, the next time I tried to open the workbook with the pivot tables, Excel went berserk and I was unable to even close it....a dialog box kept appearing.

    Eventually after I used Task Manager to kill the process, I discovered I had lost a whole mapped drive - all of the files were gone, nothing was in the Recycle Bin.

    BTW: this problem is not due to the VBA which was running for 4 years without problems. It has something to do with moving from Excel 2010 to Excel 2016 or something to do with the "Open And Repair" facility.

    I am going to try copying the worksheets which are used as the source data and then changing the Pivot Table data source to the new worksheet.....perhaps this will trigger a new pivotcache to be constructed. I am almost positive that is where the problem lies.

    Tuesday, May 9, 2017 8:09 PM
  • Hi Syswizard,

    it looks like when you try to change the pivot cache it get corrupted.

    can you show me how you try to change the pivot cache.

    it is possible that something get change between Excel 2010 and Excel 2016.

    and because of that your code is not able to work with newer version.

    if you can try to provide sample code then we can try to test it and can see if we can correct it.

    I don't think that the issue is related with "Open and Repair" because it get corrupted first and then after maybe you use "Open and Repair".

    without testing the code we are not able to provide accurate suggestion and cause for this issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 10, 2017 8:35 AM
    Moderator
  • I created a VBA procedure to copy all of the properties of a PivotTable along with the fields.  It then Deletes that PivotTable and rebuilds it from the Properties.

    Although it recreated the layout perfectly, it displays no data. I checked the data source and it is valid. If I build it manually, the data renders fine.

    What could be happening ? What did I forget ? Once again, I suspect it's something to do with the PivotCache.

    Thursday, May 11, 2017 8:45 PM
  • I finally discovered the problem.

    It's a long, long-standing problem with assigning page-level fields in a PivotTable from VBA:

    oShtReview.PivotTables("pvTrades").PivotFields("ClientName").CurrentPage = [ClientName].Value

    If [ClientName].Value is not in the Pivot Table page-level Filter items, Excel will generate it !!
    That is fatal....as now it's internal index is "off".

    The solution....is very simple:
    Move the page-level field to row-level.....and then Sort it.
    Then drag it back to the page-level.

    A better solution: Excel should have a property to insure new entries cannot be inserted if the page-level filter is assigned a value not in the item list. Right now one would have to iterate the entire page-level filter's item list before making the assignment. That would be prohibitively slow.

    Friday, May 12, 2017 10:45 PM
  • Hi Syswizard,

    from your last reply , I find that you had find the root cause and solution for your issue by your self.

    I suggest you to mark your last reply as an answer.

    so that we can close this thread.

    if you not mark the answer then thread will be remain open.

    I also find that you have a suggestion regarding Excel Application.

    "A better solution: Excel should have a property to insure new entries cannot be inserted if the page-level filter is assigned a value not in the item list. Right now one would have to iterate the entire page-level filter's item list before making the assignment. That would be prohibitively slow."

    please try to submit your feedback on link below.

    Welcome to Excel’s Suggestion Box!

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 15, 2017 2:07 AM
    Moderator