[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1376
  • 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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