How to Read Data from Pivot Table Using VBA in Excel




how-to-read-data-from-pivot-using-vba
Reading pivot tables using vba can be quite easy. Here are a few ways you can do it.

You may also want to read my previous article on formatting pivots.

(Before you dive further, you may want to download this example which will guide you through some of the steps in reading a pivot table data using vba)

The Pivot Table Hierarchy

Lets look at this peice of code:

Sub ListAllItemObjects()
For Each pvt In ActiveSheet.PivotTables
For Each fld In pvt.PivotFields
For Each itm In fld.PivotItems
MsgBox itm
Next itm
Next fld
Next pvt
End Sub


What we’ve done in the above code is to recurse through all the pivots in the active sheet, all pivots fields in each of the pivots and finally through all the the pivot items in each of the pivot fields. If you wanted to just work with a single pivot, field or item, you can simply access it by using something similar to pivottable(i).pivotfield(j).pivotfield(k).select instead.

Once you know this much, the rest is fairly easy. Just bear in mind that rather than using pivottable.pivotfields you can be more specific.

pivot-table-page-row-column-data-fields

Since we know that a pivot table has page fields, row fields, column fields and data fields, we can use pivottable.pagefields or pivottable.rowfields or pivottable.columnfields or pivottable.datafields. Let’s look at how each one works:

Accessing the page field:

ActiveSheet.PivotTables(“PivotTable1”).PageFields(i).PivotItems(j)

Accessing the row field:

ActiveSheet.PivotTables(“PivotTable1”).RowFields(i).PivotItems(j)


Accessing the column field:

ActiveSheet.PivotTables(“PivotTable1”).ColumnFields(i).PivotItems(j)


Accessing the data field:

ActiveSheet.PivotTables(“PivotTable1”).DataFields(i).PivotItems(j)


Keeping a tab on the index is very important….the index starts at 1 and not 0. So to access the second row field use index (2) and not index (1).



That was about reading the fields, what about reading the data contained within the pivot. Well that can be slightly tricky. VBA uses pivot.getpivotdata function to retreive values from the pivot. It is the same function that is used by Excel. The syntax, when used in Excel is GetPivotData ( data_field, pivot_table, “Field1”, “Property1”, “Field2”, “Property2″,……”FieldN”, “PropertyN” ). This when used in VBA is slightly different. You don’t need to specify the name of the pivot table as the function, by the virtue being called by a pivot object, ‘knows’ which pivot to return values from.


The syntax for GetPivotData function when used in excel :
GetPivotData ( data_field, pivot_table, “Field1”, “Property1”, “Field2”, “Property2″,……”FieldN”, “PropertyN” )

The syntax for GetPivotData function when used in VBA :
PivotTable.GetPivotData ( data_field, “Field1”, “Property1”, “Field2”, “Property2″,……”FieldN”, “PropertyN” )



The most important point is know that only those values can be retreived which appear on the excel worksheet. For example if the a row total has been turned ON for a pivot on the excel sheet, you can write the vba code to retreive the total for the row. However, the moment you turn the row total OFF, the vba formula will result in an error. (Tip: Use On Error Resume Next to override errors). You can’t write a single vba function that will return a single aggregated value from multiple cells. In short, always use a combination of column and row fields in the GetPivotData function that results in single cell value to be retrieved (that can also be located on screen).


To recap:

The pivot hierarchy is :
PivotTable -> PivotField -> PivotItem

To read data from pivot:
Pivot.GetPivotData(“Row1”, “RowProperty1”, “Row2”, “RowProperty2″,………RowN”, “RowProperty2N” …………….”Column1″, “ColumnProperty1”, “Column2”, “ColumnProperty2″……… “ColumnN”, “ColumnPropertyN”)

Incase you missed it earlier, you can download an example of how to read data from pivot table using VBA here.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. Mako wrote:

    Very informative. And thanks for the example. Will try some of these on my dashboards.

  2. William wrote:

    Hello,

    I am trying to manage the format for a pivot table that we receive from a customer every week. In order for my macro to function correctly, I need to be able to format the pivot table to the expected format, and I’ve been able to do this for rows, autoformat, etc., but not for data or columns.

    The data fields change once a month (which is expected), but I need to be able to remove any column fields without removing any data fields.

    Therefore, I have the following code to remove column fields:

    1
    2
    3
    4
    5
    6
    Private Sub removepivotfields(pvtable As PivotTable)
        Dim pvtfield As PivotField
        For Each pvtfield In pvtable.ColumnFields
            pvtfield.Orientation = xlHidden
        Next
    End Sub

    However, this also for some reason deletes my fields in pivotdata as well.

    To compensate, I tried the following code:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    Private Sub removepivotfields(pvtable As PivotTable)
        Dim pvtfield As PivotField
        Dim datafields() As String    
        ReDim datafields(1 To 1)
        For Each pvtfield In pvtable.datafields
            storedatafields pvtfield.Name, datafields
        Next
        For Each pvtfield In pvtable.ColumnFields
            If checkdatafields(pvtable.Name, datafields) = True Then
                pvtfield.Orientation = xlHidden
            End If
        Next
    End Sub

    Private Function checkdatafields(pivottablename As String, datafields As Variant) As Boolean
        Dim i As Integer
        checkdatafields = False
        For i = LBound(datafields) To UBound(datafields)
            If datafields(i) = pivottablename Then
                checkdatafields = True
                Exit For
            End If
        Next
    End Function

    Private Sub storedatafields(ByRef pvfieldname As String, datafields As Variant)
        Dim i As Integer
       
        If datafields(1) = "" Then
            i = UBound(datafields)
        Else
            i = i + 1
        End If
        ReDim Preserve datafields(1 To i)
        datafields(i) = pvfieldname
    End Sub

    However, now it will not remove any column fields, thinking that they are data fields.

    Is there any way in VBA to systematically differentiate between column fields and data fields so I can remove column fields while leaving data fields as is?

    Thanks.

    Regards,

    William

  3. Ajay wrote:

    William,

    Welcome to da TaB. I am certain that the first piece of code should work. I modified the code that you mentioned in the first block and it works fine.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Sub removepivotfields()
        Dim pvttable As PivotTable
        Dim pvtfield As PivotField
       
        For Each pvttable In ActiveSheet.PivotTables
            For Each pvtfield In pvttable.ColumnFields
                pvtfield.Orientation = xlHidden
            Next
        Next
    End Sub

    Here’s the workbook with code to remove columns from pivot table using vba. What you’ve got is the right code if all you want to do is to hide (or show) certain (or all) columns. If you remove all the columns, the data should simply aggregate with the final total remaining the same.

    The second part of the code is unnecessary. pvttable.ColumnField should be sufficient to give you a handle to the column fields.

    My only guess if that you may want to double check the object that you are passing as parameter to the function.

    Regards,

  4. JMP wrote:

    First of all congrats to this great article! Nice VBA coding, I really could use a lot of that!
    But I still have one question, maybe You know the answer: Is it possible to get the address of the target of a GETPIVOTDATA-formula? I would like to use its address to start the Pivot Elements Drill-Down via a Hyperlink.

    Hope I could express what I am searching for!

    Regards,
    JMP

  5. Ajay wrote:

    JMP – That’s a very interesting question. I don’t have a ready answer for that but let me give it a try.

    In the meanwhile could you please let me know whether you just have the cell where the GETPIVOTDATA is located or do you have the list of all the parameters to start with. If you have all the parameters separately available, one can try sorting through the entire pivot table column and row range and then try locating the cell that lies at their intersection. I agree that’s not the best of solutions but off hand that’s what I can think of till you provide me with more specifics or possibly a link to a file with the pivot table which I can have a look at.

    Regards,

  6. JMP wrote:

    Dear Ajay,
    sorry for my late reply.

    Yes I have the cell located containing the GETPIVOTDATA formula. Unfortunatelly the targeted sheet contains various Pivot Tables. Therefore a Lookup scanning through the Rows and Columns is not possible, because the specific lables might occur several times within the sheet.
    On the other hand does the GETPIVOTDATA formula contain a specific link to a used range (in this case the specific Pivot Table) due to its synthax. in this table the row and column lables just occur once. My problem is that my coding skills are too bad to realize this “Pivot-internal lookup” without making it fo ALL Pivots usable…

    Hope I could express my problem in more detail.

    Regards,
    JMP

  7. Ajay wrote:

    Jim,
    Here’s something that should help.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    Function get_pivot_area(rng As Range) As String
    Dim cell_formula As String
    Dim getpivot_link_start, getpivot_link_end As Long
    Dim pvt As PivotTable
    Dim rng2 As Range
    cell_formula = rng.Cells(1, 1).Formula

    getpivot_link_start = Application.WorksheetFunction.Find(",", cell_formula)
    getpivot_link_end = Application.WorksheetFunction.Find(",", cell_formula, getpivot_link_start + 1)

    For Each pvt In ActiveSheet.PivotTables
    If InRange(Range(Mid(cell_formula, getpivot_link_start + 1, getpivot_link_end - getpivot_link_start - 1)), pvt.TableRange1) = True Then
    get_pivot_area = CStr(pvt.TableRange1.Address)
    End If
    Next pvt
    End Function


    Function InRange(Range1 As Range, Range2 As Range) As Boolean
    ' returns True if Range1 is within Range2
    Dim InterSectRange As Range
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
    End Function

    Open the VBA IDE and place this code in a module. Now say if the pivot table is placed somewhere in the sheet and a particular cell (say “A15”) has the getpivotdata formula which refers to this particular pivot table, you can then simply enter =get_pivot_area(A15) in any cell. This User Defined Function (UDF) would return the entire area range of the pivot table to which the getpivotdata formula points to. You can then use the returned area range with a couple of OFFSET formulas.

    Does this help :roll:
    Regards,

  8. Jman wrote:

    I am trying to write VBA code that would drill down the data from a pivot table column or row total. I need to drill these totals down for further processing via VBA macro’s. However, I cannot seem to find a function that will drill down in VBA. Any suggestions?

  9. carlitos wrote:

    For me this is amazing…
    thanks a lot for helping us…

  10. Sayyad wrote:

    Ajay,

    Is it possible to loop all fields in VBA such that all of them get added to the pivot table and summed (or counted, average etc.)? I regularly deal with 100+ variables, and it’s terribly painful to keep adding them one by one to the pivot table.

    Thanks,

  11. JD wrote:

    Hi there,
    found this page when searching for a solution to my problem.
    But still not what I am looking for :(
    My Problem:
    I have an Access database with Input table, allocation queries and pivottables to get the data “summed if”.
    Now I would like to export what the pivottable is showing but I somehow can’t get the data out of it….

    Is it possible to create a table (in access) out of the other pivot table?
    like:
    company, account, total (this total is calculated in the pivot, same company same account)

    i don’t know if it is understandable what I try to do :) please come back if you need any further info

    Thanks!
    Cheers
    JD

  12. Megha wrote:

    Hi,
    Very informative. I could implement my code using the article.
    But I have ran into a problem. I have filtered the pivot table using a report filter. When I run the code it is picking all the rows that are not visible due to filter as well.

    Please help.

  13. Jossy wrote:

    Using the sample “How to read data..” file I downloaded from your website, referring to pivot2, what is the correct VBA code if I am writing a macro to filter column F (Science) make on those scores above 65.00 visible? Thanks in advance.

  14. XLE_22 wrote:

    Very interesting and very useful article.
    I found all necessary information to deal with pivottables from VBA.
    Thanks a lot for sharing your EXCEL/VBA example so educational and efficient. ;o)

  15. Charles DiGiovanna wrote:

    Initially, I was unable to use the code with Option Explicit invoked because there is no information relating to initializing the term “pvt”. I finally figured it out but I find failing to initialize variables an unfortunate and unnecessary common error in many published VBA code examples.

  16. Robin wrote:

    I dont understand this code:

    1
    ActiveSheet.PivotTables(“PivotTable1”).DataFields(i).PivotItems(j)

    its not working. How can you have pivotitems for a datafield??

  17. Chris wrote:

    Great article! Very, very helpful.

    I applied the concepts here to write the following code to try returning the rowfield of a cell in the datafield. I wouldn’t think this would be that bad because I can see the field information when I hover over a cell and the contextures box is displayed. My code below worked just fine if the rowfield index and rowitem index are the same, and they usually are, but I came across a time where they were not the same. I thought that the index of the rowfield is changed whenever you add/remove a field from the row section of the pivot table. However, that didn’t seem to be the case when I added one field in particular – when I added it, all other rowfields’ indexes would adjust based on what position I put the new field in, but the new field would always have an index of 1, even if I put it in the 4th position. Are index and position not the same? Can you think of a way to pull the rowfield based on a cell in a data field you select?

    The purpose of this is to pull the rowfield and rowitem (and columnfield, columnitem, pagefield, and pageitem eventually) to compile a SQL statement to query on an Access database if users want to see very detailed information relating to expenses for their budget.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    Sub ActualDetailDrill()

    Dim NumOfRowItems As Integer
    Dim NumOfColItems As Integer
    Dim NumOfPageFields As Integer
    Dim Field As PivotField
    Dim ActualDrillActiveCell As Range

    Set ActualDrillActiveCell = Sheets("ActualDrill SQL Build").Range("A1")
    NumOfRowItems = ActiveCell.PivotCell.RowItems.Count
    i = 1
    Do Until i > NumOfRowItems
        ActualDrillActiveCell.Value = ActiveCell.PivotTable.RowFields(i).Name
        ActualDrillActiveCell.Offset(0, 1).Value = ActiveCell.PivotCell.RowItems(i).Name
        ActualDrillActiveCell = ActualDrillActiveCell.Offset(1, 0)
        i = i + 1
    Loop

    End Sub
  1. da TaB is On » How to Format a Pivot Table in Excel wrote:

    […] comes to using pivot tables and what can be achieved. You may also want to read my earlier post on reading data from pivot table using vba to know how you can get data out of a pivot table using vba for some of your advanced dashboards. […]

  2. da TaB is On » How to Create a Dashboard in Excel wrote:

    […] -> ‘PivotTable and PivotChart Report’ option from the menubar. You can read more about pivot tables and VBA […]

  3. How To Create Pivot Table Using VBA | da TaB is On wrote:

    […] more about reading data from pivot table A Few More Interesting ArticlesRead Write to File, Excel Workbook, Access using Javascript & […]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES