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)

Lets look at this peice of code:

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.

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:

Accessing the row field:

Accessing the column field:

Accessing the data field:

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).

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.