Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1329
  • Last Modified:

Excel: Loading pivotcache data into vba array

Hi,
I would like to load the data from an existing pivot cache directly into an array using vba code.  I would be very grateful for any help.

Thanks in advance!
Gary
0
GaryAA
Asked:
GaryAA
  • 2
1 Solution
 
Rory ArchibaldCommented:
Unless the pivotcache is created from an ADO recordset, you can't do that - you'll have to extract the data from its original source.
0
 
GaryAAAuthor Commented:
Thanks for your answer, from my rsearch that appears to be true.  With that in mind, how about loading an array directly from the pivot table itself using vba code in a very automated manner.  I know how to load a range into an array as follows:

Dim LoadArray1 As Variant
LoadArray1 = Range("datarange")

Is there anything similar to the range concept except with a pivot table?
0
 
Rory ArchibaldCommented:
You can load from the table since it occupies a range, but all you will get is whatever is displayed in the table, not the underlying records. If that's what you want then you use the same method you mentioned with whichever range you are after - the TableRange1, TableRange2, DataBodyRange and so on. For example:

LoadArray1 = Activesheet.PivotTables(1).DataBodyRange.Value
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now