How can I retrieve the field that is being summed in Pivot Table Values area using VBA

I have a Pivot Table. I can read the Row and Column values by iterating the PivotFields collection and checking the Orientation for Row and Column. What I can't find is the information pertaining to the DataField.

If I wanted to create a DataField using VBA, I could use code similar to the following to place a Field in the Values area of the PivotTable.

    With ActiveSheet.PivotTables("PivotTable1")
        Call .AddDataField( .PivotFields("Sales"), _
                            "Sum of Sales", _
                            xlSum _
                          )
    End With

The above code is an example of creating a DataField only, showing the 3 pieces of information I need to be able to read out of a given PivotTable when I do not know how it was constructed.

In the example code above, how would I retrieve the Sales field from the PivotTable properties when I don't know it before hand? The Orientation is flagged as Hidden like all of the other unused but available Fields from the PivotTable Source Data.

The PivotTable has a DataPivotField property but I cannot find a list of its Fields anywhere.

If you want to explore PivotFields in an unknown Workbook, you can use the code below:

Public Sub PrintPivotTablesOfThisWorkbook()
    Dim WS As Worksheet: For Each WS In ThisWorkbook.Worksheets
        PrintPivotTablesOfWorksheet WS
    Next
End Sub

Public Sub PrintPivotTablesOfWorksheet(WS As Worksheet)
    Debug.Print "Worksheet named '" & WS.Name & "' has " & WS.PivotTables.Count & " PivotTables"
    Dim PT As PivotTable: For Each PT In WS.PivotTables
        Debug.Print "  PivotTable named '" & PT.Name & "' at " & PT.TableRange2.Address(False, False, xlA1, True)
        Debug.Print "    has " & PT.PageFields.Count & " PageFields:"
        Dim PF As PivotField: For Each PF In PT.PageFields
            Debug.Print "      PivotField named '" & PF.Name & "' " & GetSourceNameClause(PF)
        Next
        Debug.Print "    has " & PT.RowFields.Count & " RowFields:"
        Dim RF As PivotField: For Each RF In PT.RowFields
            Debug.Print "      PivotField named '" & RF.Name & "' " & GetSourceNameClause(RF)
        Next
        Debug.Print "    has " & PT.ColumnFields.Count & " ColumnFields:"
        Dim CF As PivotField: For Each CF In PT.ColumnFields
            Debug.Print "      PivotField named '" & CF.Name & "' " & GetSourceNameClause(CF)
        Next
        Debug.Print "    has " & PT.DataFields.Count & " DataFields:"
        Dim DF As PivotField: For Each DF In PT.DataFields
            Debug.Print "      PivotField named '" & DF.Name & "' " & GetSourceNameClause(DF) & " " & GetConsolidateFunctionClause(DF)
        Next
    Next
End Sub

Public Function GetSourceNameClause(Target As PivotField) As String
    Dim Result As String: Result = ""
    Dim SN As Variant: SN = GetSourceNameSafely(Target)
    If IsNull(SN) Then
        Result = "without a SourceName"
    Else
        Result = "with SourceName '" & SN & "'"
    End If
    GetSourceNameClause = Result
End Function

Public Function GetSourceNameSafely(Target As PivotField) As Variant
    Dim Result As Variant: Result = Null
    On Error Resume Next
        Result = Target.SourceName
    On Error GoTo 0
    GetSourceNameSafely = Result
End Function

Public Function GetConsolidateFunctionClause(Target As PivotField) As String
    Dim Result As String: Result = ""
    Dim FN As String: FN = GetConsolidationFunctionAsSring(Target.Function)
    If FN = "Invalid" Then
        Result = "without any Function"
    Else
        Result = "with Function " & FN
    End If
    GetConsolidateFunctionClause = Result
End Function

Public Function GetConsolidationFunctionAsSring(ConsolidationFunction As XlConsolidationFunction) As String
    Dim Result As String: Result = "Invalid"
    On Error Resume Next
        Select Case ConsolidationFunction
        Case xlAverage
            Result = "xlAverage"
        Case xlCount
            Result = "xlCount"
        Case xlCountNums
            Result = "xlCountNums"
        Case xlDistinctCount
            Result = "xlDistinctCount"
        Case xlMax
            Result = "xlMax"
        Case xlMin
            Result = "xlMin"
        Case xlProduct
            Result = "xlProduct"
        Case xlStDev
            Result = "xlStDev"
        Case xlStDevP
            Result = "xlStDevP"
        Case xlSum
            Result = "xlSum"
        Case xlUnknown
            Result = "xlUnknown"
        Case xlVar
            Result = "xlVar"
        Case xlVarP
            Result = "xlVarP"
        End Select
    On Error GoTo 0
    GetConsolidationFunctionAsSring = Result
End Function

After inserting the above into a Module; simply type PrintPivotTablesOfThisWorkbook in the Immediate Window, and it will enumerate all PivotTables in the workbook; or type PrintPivotTablesOfWorksheet ActiveSheet to list only PivotTables on the ActiveSheet.

It is easy to find the object if you know the name before hand. But I want to be able to find it by reading the PivotTable properties.

I have made a little progress as shown by the code below. But neither the PivotItem or PivotField objects expose the Function correctly, which should be xlSum. In either object, the Function property is an error.

To simplify the code below, I used Item(1) in every case. In production code, I would have some kind of iteration logic as appropriate.

Dim oPivotTable As PivotTable
Dim oPivotField As PivotField
Dim oPivotItem As PivotItem

    Set oPivotTable = ActiveSheet.PivotTables.Item(1)
    With oPivotTable
        With .DataPivotField.PivotItems
            Set oPivotItem = .Item(1)
            With oPivotItem
                Set oPivotField = oPivotTable.PivotFields.Item(oPivotItem.Name)
            End With
        End With
    End With

I finally found it! oPivotItem.DataRange.PivotField.Function returns the correct value. Also, oPivotItem.DataRange.PivotField.SourceName returns the Field name.

So, from the original post, the 3 associated values are

    1. oPivotItem.DataRange.PivotField.SourceName, 
    2. oPivotItem.Name and
    3. oPivotItem.DataRange.PivotField.Function