XL2000: Data Fields Are Not Included in PivotFields Collection (213455)

WORKAROUND

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 the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: 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:

For Each x In Pivot1.VisibleFields