excel - 从 Vba 中的数据透视表中检索数据

Question

我有一个 Excel 工作表，其中包含三个数据透视表，数据透视表名称为 PivotTable1...PivotTable3，事件字段名称分别为 Country、Language 和 Printers。我需要的是获取每个数据透视表中的所有数据到每个字符串或字符串数​​组。任何帮助将非常感激。

Answer 1

一个快速而肮脏的让你去的；一个线性字符串中的数据透视表的所有单元格，用“;”分隔。这应该为使用哪些方法和属性提供足够的灵感。当心:Tmp无法容纳无限大的数据透视表，如果它们变得非常大，请考虑编写 Tmp改为文件。

Sub PTTest()
Dim SH As Worksheet ' the current worksheet from the colection of workbooks
Dim PT As PivotTable ' the current pivot table from the current worksheet
Dim PTC As Range  ' the cell range of the current pivot table
Dim Tmp As String ' the buffer for concatenated cell values

    Tmp = ""
    ' process all sheets, as Pivot table objects are contained by sheets
    For Each SH In ActiveWorkbook.Worksheets

        For Each PT In SH.PivotTables

            For Each PTC In PT.TableRange1.Cells
                ' all cells in one buffer, seperated by ";"
                ' if you want to include page header cells, use
                ' "PT.TableRange2.Cells" instead
                Tmp = Tmp & PTC & ";"
            Next PTC

            ' *** do something *** with the buffer
            ' ok very simple we print it into the debugger's Immediate window
            Debug.Print Tmp

            ' empty buffer for next pivot table
            Tmp = ""

        Next PT
    Next SH
End Sub

希望对您有所帮助....祝您好运 MikeD