Microsoft KB Archive/213455

= XL2000: Data Fields Are Not Included in PivotFields Collection =

Article ID: 213455

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213455





SYMPTOMS
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.



CAUSE
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.



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:

https://partner.microsoft.com/global/30000104

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:  Start Excel.  Create the following spreadsheet:   A1: Name   B1: Sales A2: Ann   B2: 45 A3: Mark  B3: 23 A4: Jan   B4: 34  Select cells A1 through B4. On the Data menu, click PivotTable and PivotChart Report. In step 1 of the PivotTable and PivotChart Wizard, accept the defaults and then click Next.</li> In step 2 of the wizard, ensure that the range is $A$1:$B$4, and then click Next.</li> In step 3 of the wizard, click Layout.</li> Drag the Name field button to the Column area of the PivotTable.</li> Drag the Sales field button to the Data area of the PivotTable.

Note that this field appears with the name "Sum of Sales."</li> Click OK.</li> Click Finish.</li> Press ALT+F11 to start the Visual Basic Editor.</li> On the Insert menu, click Module.</li>  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 </li> Press ALT+F11 to return to Excel.</li> On the Tools menu, point to Macro, and then click Macros.</li> 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".</li></ol>

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

<div class="status_section">

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

<div class="moreinformation_section">

MORE INFORMATION
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.

<div class="references_section">