Excel Pivot Table - ordinal position of PivotItems does not match what is diaplayed on the screen
When comparing: PivotTable.PivotFields("field1").PivotItems
to: PivotTable.PivotFields("field2").PivotItemsThe ordinal positions of the items when consumed from VBA do not match that which is displayed on the screen.
Note that in the code below, I have explicitly set both PivotFields to visible (xlColumnField), and set the position of one of them to 1.
On the screen, the position of the PivotItems within each of the two PivotFields lines up accordingly (obviously). But when in this state, you examine the items through VBA, the ordinal position within each collection, as well as the .Position Property, are different than what is displayed on the screen.
Is there a way to properly access these so I can find proper matches between the individual PivotItems across multiple PivotFields? (Or some other way of going about it?)
Sub debugPivotTableFieldData(pt As PivotTable)
Dim aPivotField As PivotField
Dim aPivotItem As PivotItem
Dim iField As Integer
Debug.Print pt.PivotFields("Airport").Orientation
Debug.Print pt.PivotFields("Name").Orientation
pt.PivotFields("Airport").Orientation = XlPivotFieldOrientation.xlColumnField
pt.PivotFields("Name").Orientation = XlPivotFieldOrientation.xlColumnField
pt.PivotFields("Airport").Position = 1
Debug.Print "DEBUG PIVOT TABLE: " & pt.Name
For iField = 1 To pt.PivotFields("Airport").PivotItems.Count
Debug.Print pt.PivotFields("Airport").PivotItems(iField) & " - " & pt.PivotFields("Airport").PivotItems(iField).Position
Next
For iField = 1 To pt.PivotFields("Name").PivotItems.Count
Debug.Print pt.PivotFields("Name").PivotItems(iField) & " - " & pt.PivotFields("Name").PivotItems(iField).Pos开发者_开发问答ition
Next
For iField = 1 To pt.PivotFields("Airport").PivotItems.Count
Debug.Print pt.PivotFields("Airport").PivotItems(iField) & " - " & pt.PivotFields("Name").PivotItems(iField)
Next
I'm 95% certain this is just the way it is. I think the ordinal position of the items in the collection has no relation whatsoever to what is displayed on the screen, or what the value of .Position is for each element.
So to properly set the column positions for the pivot items in two pivot fields at once, I filled a Scripting.Dictionary with the distinct values (of the two fields) from the PivotTable.PivotCache.Recordset....then, iterate on the dictionary items, and you can access the the PivotItems in each pivot field by name and set their .Position that way so they have matching sorts.
精彩评论