Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Recreate Source Data from PivotTable Cache

I am trying to extract the source data from a PivotTable that uses a PivotTable 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 achieve this?

like image 506
Kuyenda Avatar asked Sep 18 '09 02:09

Kuyenda


People also ask

How do I access the pivot cache?

Use these steps when you want to create an additional pivot table with a separate pivot cache while using the same data source. Select any cell in the data and press ALT + D + P. This will open the Pivot Table and Pivot Chart Wizard.

How do I separate pivot table cache?

Click any cell in the PivotTable report for which you want to unshare the data cache. On the Options tab, in the Data group, click Change Data Source, and then click Change Data Source. The Change PivotTable Data source dialog box appears.

How do I add source data to an existing pivot table?

Next to the pivot table, click Edit to open the pivot table editor. Add data—Depending on where you want to add data, under Rows, Columns, or Values, click Add. Change row or column names—Double-click a Row or Column name and enter a new name. under Order or Sort by and select the option or item.

How do I refresh pivot table data source?

Click Analyze > Refresh, or press Alt+F5. Tip: You can also refresh the PivotTable by right-clicking on the PivotTable, and then selecting Refresh. To update all PivotTables in your workbook at once, click Analyze > Refresh arrow > Refresh All.


1 Answers

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
like image 182
Kuyenda Avatar answered Nov 26 '22 19:11

Kuyenda