首页 文章

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

提问于
浏览
2

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

1 回答

  • 2

    快速而肮脏的一个让你前进;一个线性字符串中的数据透视表的所有单元格,由";"分隔 . 这应该为使用哪些方法和属性提供足够的灵感 . 注意: 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

相关问题