XL2000: Data Fields Are Not Included in PivotFields Collection

In Microsoft Excel, when you run a Microsoft Visual Basic for Applications macro that uses the PivotFields collection to return the fields in a PivotTable, the fields in the data area (DataFields object) are not returned.

This behavior can occur because the PivotFields collection does not contain the DataFields object or the fields that are in the data area. Data fields are created from pivot fields. For example, the data field "Sum of Profit" is created by dragging the pivot field "Profit" to the data area on the PivotTable in the PivotTable Wizard. Multiple data fields may be created from the same pivot field.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS You can create a macro that returns only the data fields in a PivotTable, by using the DataFields object.

To create and run the macro DisplayDataFields, use the steps in the following example:

1. Start Excel.
2. Create the following spreadsheet:

      A1: Name   B1: Sales
      A2: Ann    B2: 45
      A3: Mark   B3: 23
      A4: Jan    B4: 34
   					

3. Select cells A1 through B4.
4. On the Data menu, click PivotTable and PivotChart Report.
5. In step 1 of the PivotTable and PivotChart Wizard, accept the defaults and then click Next.
6. In step 2 of the wizard, ensure that the range is $A$1:$B$4, and then click Next.
7. In step 3 of the wizard, click Layout.
8. Drag the Name field button to the Column area of the PivotTable.
9. Drag the Sales field button to the Data area of the PivotTable.
   
   Note that this field appears with the name "Sum of Sales."
10. Click OK.
11. Click Finish.
12. Press ALT+F11 to start the Visual Basic Editor.
13. On the Insert menu, click Module.
14. In the module sheet, enter the following code:

    Sub DisplayDataFields()
        Dim x As Variant
        Dim Pivot1 As PivotTable
        Set Pivot1 = Worksheets(1).PivotTables(1)
        For Each x In Pivot1.DataFields
            'Display pivot field name (name of field in table)
            MsgBox "Pivotfield Name is " & x.Name
            'Display pivot field source name (name of field in original
            'data)
            MsgBox "Pivotfield SourceName is " & x.SourceName
        Next
    End Sub
    					

15. Press ALT+F11 to return to Excel.
16. On the Tools menu, point to Macro, and then click Macros.
17. Click DisplayDataFields, and then click Run. The dialog boxes that appear with the name and source name of the fields in the PivotTable, show the name as "Sum of Sales" and the source name as "Sales".

To return the pivot fields that are visible in the PivotTable itself, use the VisibleFields property in place of the DataFields property, as in the following line of code:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

You can use the SourceName property of the PivotField object to return the name of the pivot field as it appears in the original source data for the pivot table. The Name property of the PivotField object returns the name of the pivot field as it appears in the table.

For more information about the PivotFields Collection Object, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type pivot fields in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the SourceName Property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type sourcename in the Office Assistant or the Answer Wizard, and then click Search to view the topic.