Importing Data from Excel PivottableCache

[dmckeever]

Anyone know how to do this?
I know that a pivot table cache exists in the source excel workbook. I cannot query the original ODBC source of the cache, but I'd like to pull the data in the cache into access either by link or import in order to use queries.

 

[stopher]

You can make the data visible in the Excel workbook.

Right Click on the "pivot table"=>"Group and Show Detail"=>"Show Detail"

The data will appear as a new sheet. Then you can point Access at this sheet.

hth
Chris

 

[stopher]

I just tried this. You need to remove all the fields from your pivot in order to show all the underlying data. i.e. you will only have Total as the row and column. I guess you can add more data items but I've not tried.

Also, the view that is created seems only to be a snapshot and does not get updated with a pivot refresh. But at least you get your data.

hth
Chris

 

[dmckeever]

Not seamless enough

In MS Access VB I can access the workbook and using the pivotcache object I can report the record count using the record count property, so I know I'm connected to the underylying data. What I'd like to do is build a form around attaching the workbook and then just link to the pivotcache and use that to for pre-defined queries that I'll create. I know how to build forms etc. what I need to understand is what methods and objects do I use to read that data.

The workbook is created for me by a third party software that I have no control over. I'd like to eliminate the user's need to even open the workbook.

D

 

[Air_Cooled_Nut]

If you go into the VBA editor in Excel and access the Help there you will get information about the pivot cache object. For example, I found the following that may be of use if you can't pull directly from the Excel sheet:

Saves the PivotTable cache source as an Microsoft Office Data Connection file.

expression.SaveAsODC(ODCFileName, Description, Keywords)
expression Required. An expression that returns one of the objects in the Applies To list.

ODCFileName Required String. Location to save the file.

Description Optional Variant. Description that will be saved in the file.

Keywords Optional Variant. Space-separated keywords that can be used to search for this file.

Example
The following example saves the cache source as an ODC file titled "ODCFile". This example assumes a PivotTable cache exists on the active worksheet.

Sub UseSaveAsODC()
    Application.ActiveWorkbook.PivotCaches.Item(1).SaveAsODC ("ODCFile")
End Sub

You could have Access open the Excel wkbk object, save the specific pivot cache, then have Access hit the saved file. I've never tried this so let us know if it works, it'd be nice to know