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:
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.
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.
Very informative. And thanks for the example. Will try some of these on my dashboards.
November 13th, 2008 at 3:15 amHello,
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:
2
3
4
5
6
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:
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
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
August 12th, 2009 at 11:19 amWilliam,
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.
2
3
4
5
6
7
8
9
10
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,
August 12th, 2009 at 11:43 pmFirst 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,
October 29th, 2009 at 6:46 amJMP
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,
October 29th, 2009 at 2:03 pmDear 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,
November 5th, 2009 at 4:46 amJMP
Jim,
Here’s something that should help.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
November 6th, 2009 at 12:00 amRegards,
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?
May 22nd, 2010 at 10:14 pmFor me this is amazing…
January 13th, 2011 at 5:31 pmthanks a lot for helping us…
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,
April 3rd, 2011 at 11:41 pm