Clever Geek Handbook

How to cycle through the middle (child) fields of an Excel table?

My ultimate goal is to populate the TreeView with its pivot table results, which means I will have parent nodes and child nodes.

Let's say I have the following pivot table results (from the table)

FirstNames

[+] Bill
[+] Bob
[-] Charles
      Charles Jameson
      Charles Johnson
      Charles Smith
[+] James
[+] Zachary

When I go through myPivotTable.PivotFields("FirstNames").PivotItems

, I go through Bill, Bob, Charles, James, Zachary. This gives me my parent nodes, but I also want my child nodes(e.g. "Charles Jameson", "Charles Johnson", and "Charles Smith").

How can I iterate over child fields?

+3


source to share


2 answers


Child nodes have their own names, which are not necessarily considered "child nodes", they are equal. So you just change the "PivotFields (" FirstNames ") part to get them. But I suppose you want them to be in the context of how they are pivot related.



It might be easier to interact with the data the way you need it instead of relying on the pivot table.

+1


source


Assuming you have something like this:

For Each pvtField in myPivotTable.PivotFields("FirstNames").PivotItems


You should just use

For Each pvtField in myPivotTable.PivotFields("FirstNames").PivotItems
    If pvtField.Orientation = xlRowField Then
       For Each pvtField2 in myPivotTable.PivotFields(pvtField.Name).PivotItems
0


source






More articles:


All Articles