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

Question

我有一个 Excel 工作表，其中包含三个数据透视表，数据透视表名称分别为数据透视表1...数据透视表3，活动字段名称分别为国家/地区、语言和打印机。我需要的是将每个数据透视表中的所有数据获取到每个字符串或字符串数​​组。任何帮助将非常感激。

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