Retrieve data from Pivot Table in Vba

I have an excel work sheet which contains three pivot tables having Pivot Table Names as PivotTable1...PivotTable3 and Active field names as Country, Language and Printers respectively. What I need is to get all data in ea开发者_Go百科ch pivot table to each string or string array. Any help will be very thankful.

A quick & dirty one to get you going; all cells of a Pivot table in one linear string, seperated by ";". This should give enough inspiration on which methods and properties to use. Beware: Tmp cannot hold indefinitely large pivot tables, if they grow enormously large, consider writing Tmp to a file instead.

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

Hope that helps .... good luck MikeD