VBA Code to return pivot table cell's row, column, and page fields and items

Question

I'm trying to write some code that will return the row, column, and page fields and items after I've highlighted a cell in the values area of the pivot table. For example, if I select a cell that contains a value of $1000, I'd like to the code to pull the row field and item (for example, the field would be Department and item would be Finance), column field and item (for example, the field would be Account and item would be Advertising) and page field and item (for example, the field would be Company and item would be XYZ, Inc.).

This seems like it should be pretty straightforward, because when you hover over any cell in a pivot table, it will show you the drilldown information in the contextures box, however, I'm having a hard time manipulating the pivot table objects because there doesn't seem to be much written on them online or by microsoft.

It seems like the pivotline or pivotlines object might be what I'm looking for, but I can't figure out how to use it.

I originally took this approach and it worked fine until I realized that a rowfields' index is not necessarily it's position in the row field, so I had to scrap this.

Sub ActualDetailDrill()
'after sub is completed, make this sub called when a cell in a pivot table is double-clicked.

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

Any help would be very, very appreciated. This is one of the last steps in a big project I'm working on that will be very helpful to the company I work for.

Solution

It's the weekend and I had some time to dig into this interesting question. I think you were pretty close by using RowItems and ColumnItems. The PivotTable.RowFields are more general though and don't apply at the PivotCell level.

I hate working with Page Fields, but think this is the correct logic for that:

Sub GetValueFieldStuff()
Dim pvtCell As Excel.PivotCell
Dim pvtTable As Excel.PivotTable
Dim pvtField As Excel.PivotField
Dim pvtItem As Excel.PivotItem
Dim pvtParentItem As Excel.PivotField
Dim i As Long

On Error Resume Next
Set pvtCell = ActiveCell.PivotCell
If Err.Number <> 0 Then
    MsgBox "The cursor needs to be in a pivot table"
    Exit Sub
End If
On Error GoTo 0

If pvtCell.PivotCellType <> xlPivotCellValue Then
    MsgBox "The cursor needs to be in a Value field cell"
    Exit Sub
End If

Set pvtTable = pvtCell.PivotTable
For Each pvtField In pvtTable.PageFields
    i = 0
    For Each pvtItem In pvtField.PivotItems
        If pvtItem.Visible Then
            i = i + 1
            Debug.Print "PageField " & pvtField.Name & " - Pivot Item " & i & " is " & pvtItem.Name
        End If
    Next pvtItem
Next pvtField

Debug.Print "Value Field Name is " & pvtCell.PivotField.Name
Debug.Print "Value Field Source is " & pvtCell.PivotField.SourceName

For i = 1 To pvtCell.RowItems.Count
    Set pvtParentItem = pvtCell.RowItems(i).Parent
    Debug.Print "Row Item " & i & " is " & pvtCell.RowItems(i).Name & ". It's parent Row Field is: " & pvtParentItem.Name
Next i

For i = 1 To pvtCell.ColumnItems.Count
    Set pvtParentItem = pvtCell.ColumnItems(i).Parent
    Debug.Print "Column Item " & i & " is " & pvtCell.ColumnItems(i).Name; ". It's parent Column Field is: " & pvtParentItem.Name
Next i
End Sub

If you aren't already, consider using the VBE's Locals Window. It's great for drilling down (and back up) in the pivot table's object model. That's how I saw that a ColumnItemis a PivotItem whoseParent is the PivotField it's in.

Comments

[jones-chris]

I appreciate the reply, Doug! I had company over this weekend and wasn't able to look into this yet, but hopefully I'll have time tonight. I just didn't want to think I was ignoring your reply - I really do appreciate the work you've put into this! ....and hopefully I don't get downvoted for posting this lol. @Doug Glancy

[Doug Glancy]

Thanks for letting me know. Yes hopefully we can fly under the radar with these frivolous, yet friendly, comments.

[jones-chris]

Again thank you for the comment, Doug. I had a moment to try this out today and I like how it returned the pivotitems at each visible layer, but that's half of what I'm looking for. I also want the rowfield, columnfield, pagefield, and datafield associated with each item. It might make more sense if I say what I'm trying to do overall with this code. I'm trying to pull the field names and items for those fields so that I can write more code to compile the field names and items into a SQL statement to query on another database for the detail behind that line. Does that help clarify?

[Doug Glancy]

I just edited and added the ColumnItem and Rowitem parent PivotField logic. To my mind the Data field is already dealt with by vtCell.PivotField.SourceName. I don't know what else you'd want for the Page fields. It seems like the SQL is something like WHERE PageField_1 IN (PagePvtItem1, PagePvtItem2, etc).

[jones-chris]

Perfect! I can definitely tweak that to make a SQL statement. I definitely remember trying the Parent and Child properties but always got an error for some reason - maybe I was too frustrated by that point lol.

[jones-chris]

Sorry, I waited to long to edit the last comment....here's the continuation of my last comment: I was on track to finding a solution of my own involving using Position and rowitems/columnitems, since the position of the rowfield/columnfield is the same as it's corresponding rowitem/columnitem, but you're approach is more direct. Thanks for the tip on the Locals window. I don't use that, but I'll start reading up on how to use it. Thanks again for your help!!!

[jones-chris]

Oh and you're right that the datafield is already dealt with with pvtCell.PivotField.SourcName. I missed that. Thanks again!