loading pivotcache data into vba array

[Rober2B]

Q:how do i load pivotcache data directly into a VBA array?

i have a pivot table with over a million rows. the connection to the original external source is inaccessible. however, the contents of the data underneath the pivot table (PivotCache?) will not change. i am aware that i can pull "tables" from the PivotCache in VBA and dump the result into worksheet ranges. can it be done in a more direct manner?

neither one of the following works:

sdarray = ActiveWorkbook.PivotCaches.Item(1).SourceData 'does not work

Dim pvtc As PivotCache, rec1 As ADODB.Recordset
Set pvtc = ActiveWorkbook.PivotCaches.Item(1)
Set rec1 = pvtc.Recordset 'does not work

any simple example that shows how to load the contents of a "table" directly into a VBA array would be very valuable.

References:
Pivot Table Update Sep 12th, 2008 01:36 PM by cgmojoco
Pivotcache data into a database Mar 11th, 2008 05:25 AM by gingerafro

also working with your vba & macros for excel 2007 book, but cannot find answer there either.

 

[JSummers]

no answer yet, just started doing same thing myself. I'm using pivot table to summarize data, which is used in complex statistical user functions.

as of now, i just know the cells where the pivot summary is printed and load that into vba range -> array (i have a function, converts range to array). This is kluge-ey. rather get the pivot data directly.

let me know if u find a solution. I'll keep looking, if only to simplify later progs.