Programatically accessing pivot table cache

[glenntb]

Greetings!

I have an Excel Pivot Table pulling data from an Access db. The source data is >65K lines (it's actually close to 200K). The original Access data is no longer available, but the Pivot table still works because the original data is stored somewhere in Excel (in a Pivot cache, maybe?).

If I double click on the bottom right hand corner total, a new sheet is inserted, and 65K worth of lines are loaded, but of course I don't get the remaining 135K lines anywhere. Is there any way to get to this data (ideally via VBA code)?

What I'd ideally like to accomplish is to programmtically search through the pivot cache data looking for one particular customer account. (Note: customer account isn't one of the Pivot Table Headings, so it isn't as simple as simply selecting the customer account # from one of the drop-down menus.)

I searched this forum, but didn't find much of anything that would point me in the right direction. The closest I found was thread707-1155428 but that thread was closed without a resolution.

Thanks in advance!

Glenn

 

[SkipVought]

Hi,

You have the Methods and Properties of a PivotCache Object as outlined in Help. That's all that is exposed in VBA.

Skip,

_[red][/red]

 

[glenntb]

Thanks Skip... I've been playing with them all day and can't seem to make anything work.

Has anyone ever successfully used the methods and properties on a PivotCache Object that might be able to give me some pointers? The help files are proving to be something less than helpful.

Glenn

 

[SkipVought]

Try

Set objPivotCache.Recordset = rstRecordset

do
  for each fld in objPivotCache.fields
    msg fld.value
  next
  objPivotCache.movenext
until objPivotCache.eof

Skip,

_[red][/red]

 

[SkipVought]

oops...

set rst = activesheet.pivottables(1).pivotcache.recordset

do
  for each fld in rst.fields
    msg fld.value
  next
  rst.movenext
until rst.eof

Skip,

_[red][/red]

 

[glenntb]

This looks like it'll do what I'm looking for, but I get an error on the first line. I also had to rewrite the loop so that Excel VBA was happy:

Set rst = ActiveSheet.PivotTables(1).PivotCache.Recordset

Do Until rst.EOF
  For Each fld In rst.fields
    MsgBox fld.Value
  Next
  rst.movenext
Loop

The error I get on the "Set rst..." line is: "#1004 Application-defined or object-defined error"

Looks like you're on the right track though! Thanks!

Glenn