Search code examples
excelvbaautomationhidden

how to use vba to call out the weird hidden Pivot table data sources

I found a weird stuation in my Excel file and just want to know how the creater made it and how to automatically solve it.

Someone keep sending me an excel file with only one sheet which contains one Pivot table. The weird thing is that I cannot find the data source which this Pivot Table should link to.

Even I tried to click "unhide" button, there is no hinden sheet in this file.

However, when I double click the cell in this Pivot, there will be one new sheet appear in this file. And this new sheet is the data source of this Pivot.

Is it possible to create a Macro to call out this hidden sheet without I double click it manually?

Thanks in advance.

Solution

  • The data is likely stored in a PivotCache - this data is not found on a worksheet but is saved as part of the file.

    https://trumpexcel.com/pivot-cache-excel/

    See previous question here on SO:

    Recreate Source Data from PivotTable Cache

    Alternatively, if you are comfortable with XML and your workbook is saved in xlsx format, you can unzip the file (add a .zip extension and double-click) and find the pivotcache details stored as XML in pivotCacheDefinition1.xml and pivotCacheRecords1.xml in the xl\pivotCache folder.