从数据透视表缓存重新创建源数据 [英] 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

文档表示PivotCache.Recordset是一个ADO类型，所以这应该工作。我的ADO库启用了引用。

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?

推荐答案

不幸的是，似乎没有办法直接在Excel中操作PivotCache。

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

我找到了一个工作。以下代码提取了工作簿中找到的每个数据透视表的枢纽高速缓存，将其放入新的数据透视表中，并创建一个数据透视字段（以确保数据透视缓存中的所有行都包含在总数中），然后触发ShowDetail创建一个包含所有数据透视表数据的新工作表。

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.

我仍然希望找到一种直接与PivotCache一起工作的方法，但这样可以完成工作。

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

这篇关于从数据透视表缓存重新创建源数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！