loading pivotcache data into vba array

Rober2B

New Member
Joined
Sep 21, 2009
Messages
1
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JSummers

New Member
Joined
Jan 4, 2011
Messages
9
:( 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,293
Members
418,187
Latest member
polks111

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top