Retrieve data saved in pivot table

[C.W.]

Is there a way to retrieve data saved with pivot table (of course the data
comes from external oledb/odbc data source). The pivotcache.RecordSet object
unfortunately is not accessible (error 1004), and hence I need another way
to access the underlying data.

One way I have managed to get it working is to move all of the pivotfields
to page level, and iterate through all possible combinations. At the data
field level, I would include a record count field. By examining
DataBodyRange.Value, I can determine whether page level pivot value
combinations are valid. Now this is an extremely clumsy way and slow way of
retrieving underlying data. Is there a cleaner way of doing it?

Thanks in advance

 

[ryguy7272]

Does this help?
http://www.contextures.com/xlPivotPlayPLUS01.html


Regards,
Ryan---

 

[C.W.]

Thanks for the link, but that's not what I am looking for. That tool allows
one to change pivot table's source, location and query. My problem is that I
have a pivot table generated by an external party (hence I have no access to
the original dataset), and I need a systematic way to retrieve the
underlying data against which the pivot table is built from. While double
clicking pivot table will retrieve the source data - but the 65K row
limitation of Excel 2003 and earlier prevents me from retrieving the full
data set. Hence I am wondering if anyone knows of a way to programmatically
retrieve all the data saved with the pivot table, other than using the
method I described which is slow and clumsy.