views:

2396

answers:

4

I am trying to extract the source data from a pivot table that uses a pivot table cache and place it into a blank spreadsheet. I tried the following but it returns an application-defined or object defined error.

ThisWorkbook.Sheets.Add.Cells(1,1).CopyFromRecordset ThisWorkbook.PivotCaches(1).Recordset

Documentation indicates that PivotCache.Recordset is an ADO type, so this ought to work. I do have the ADO library enabled in references.

Any suggestions on how to acheive this would be greatly appreciated.

Thanks.

A: 

What is the value at runtime of ThisWorkbook.PivotCaches(1).Recordset?

shahkalpesh
It's a recordset with recordcount = 49, but if I try to do the following I also get application-defined or object-defined error: Dim objRecordset As ADODB.Recordset Set objRecordset = ThisWorkbook.PivotCaches(1).Recordset
Kuyenda
Put the ThisWorkbook.PivotCaches(1).Recordset into watch window to see what exact type it is. That should help.
shahkalpesh
At runtime, the variable type is Integer/Variant. Of course objRecordset is type recordset. As soon as the app runs ThisWorkbook.PivotCaches(1).Recordset shows Application-defined or Object-defined error in the watch window. Leads me to believe that maybe I am forgetting a reference. Perhaps something specific to PivotCache? Thanks for your help.
Kuyenda
+2  A: 

Go to the Immediate Window and type

?thisworkbook.PivotCaches(1).QueryType

If you get something other than 7 (xlADORecordset), then the Recordset property does not apply to this type of PivotCache and will return that error.

If you get an error on that line, then your PivotCache is not based on external data at all.

If your source data comes from ThisWorkbook (i.e. Excel data), then you can use

?thisworkbook.PivotCaches(1).SourceData

To create a range object and loop through it.

If your QueryType is 1 (xlODBCQuery), then SourceData will contain the connection string and commandtext for you to create and ADO recordset, like this:

Sub DumpODBCPivotCache()

    Dim pc As PivotCache
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set pc = ThisWorkbook.PivotCaches(1)
    Set cn = New ADODB.Connection
    cn.Open pc.SourceData(1)
    Set rs = cn.Execute(pc.SourceData(2))

    Sheet2.Range("a1").CopyFromRecordset rs

    rs.Close
    cn.Close

    Set rs = Nothing
    Set cn = Nothing

End Sub

You need the ADO reference, but you said you already have that set.

Dick Kusleika
Debug.Print ThisWorkbook.PivotCaches(1).QueryType throws an error.PivotCaches(1).SourceData returns the path to the external workbook on which the source data was stored. The pivot table was created using data from an external workbook (PivotTable(1).SaveData is True) which has since been deleted. I want to recreate the original source data from the pivot cache.When you say "create a range object from SourceData and loop through it" I am guessing you mean use Range(SourceData) to reference the range object specified by SourceData, but that is not available to me.Thanks for your help.
Kuyenda
If you double-click on a cell in a pivot table, or right-click and select "Show Detail" Excel exports the source data for that calculation onto a new spreadsheet in the same workbook. You can only do this one cell at a time, however. I need to do this for the entire pivot table and then automate the process.
Kuyenda
+1  A: 

Unfortunately, there appears to be no way to directly manipulate PivotCache in Excel.

I did find a work around. The following code extracts the the pivot cache for every pivot table found in a workbook, puts it into a new pivot table and creates only one pivot field (to ensure that all rows from the pivot cache are incorporated in the total), and then fires ShowDetail, which creates a new sheet with all of the pivot table's data in.

I would still like to find a way to work directly with PivotCache but this gets the job done.

Public Sub ExtractPivotTableData()

    Dim objActiveBook As Workbook
    Dim objSheet As Worksheet
    Dim objPivotTable As PivotTable
    Dim objTempSheet As Worksheet
    Dim objTempPivot As PivotTable

    If TypeName(Application.Selection) <> "Range" Then
        Beep
        Exit Sub
    ElseIf WorksheetFunction.CountA(Cells) = 0 Then
        Beep
        Exit Sub
    Else
        Set objActiveBook = ActiveWorkbook
    End If

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    For Each objSheet In objActiveBook.Sheets
        For Each objPivotTable In objSheet.PivotTables
            With objActiveBook.Sheets.Add(, objSheet)
                With objPivotTable.PivotCache.CreatePivotTable(.Range("A1"))
                    .AddDataField .PivotFields(1)
                End With
                .Range("B2").ShowDetail = True
                objActiveBook.Sheets(.Index - 1).Name = "SOURCE DATA FOR SHEET " & objSheet.Index
                objActiveBook.Sheets(.Index - 1).Tab.Color = 255
                .Delete
            End With
        Next
    Next

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

End Sub
Kuyenda
A: 

I need to throw the Cache of the Pivot table into an Access Table because there is to many row for Excel 2003. Is this feasible, I have good knowledge of VBA but could not have it to work this way.

Thanks

christian