Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable: ChildItems and ParentItem don't work
Hi
I'm programming creation and formatting of Excel pivot-tables, using an Excel datasource. I have three dimensions as Row fields in the pivot table. The top dimension (ClientType) has two members: Segregated and Non-Seg. I'd like to hide the third level of detail for all items (Clients) which are children of the Segregated top-level member. I thought this code would do the trick: Dim objClient As Excel.PivotItem For Each objClient In Worksheets("Value").PivotTables("PTValue").PivotFi elds("ClientType").PivotItems("Seg").ChildItems objClient.HideDetail Next objClient This runs without error, but doesn't do anything! If I break in this procedure it's obvious why nothing is happening - I ask ? Worksheets("Value").PivotTables("PTValue").PivotFi elds("ClientType").PivotItems("Seg").ChildItems.Co unt in the Immediate window, and get 0. Excel thinks there are no ChildItems, when clearly there are (3 of them). I thought I'd try to use the second-level (Client)'s ParentItem property instead - iterate through all Clients, and if the ParentItem is "Seg", then do a HideDetail. Here's the code: Dim objClient As Excel.PivotItem For Each objClient In Worksheets("Value").PivotTables("PTValue").PivotFi elds("Policy").PivotItems - If objClient.ParentItem.Name = "Seg" Then objClient.ShowDetails = False Next objClient But the line marked - gives an error: 1004 "Unable to get the ParentItem property of the PivotItem class". I check objClient's Name in the Immediate window to check I've got the PivotItem I think I should - I look at the pivottable, and that item clearly DOES have a ParentItem. There's no documentation of this problem anywhere (Microsoft or Google). What is going on? I suspect that the ChildItems and ParentItem properties simply don't work, but since few people use them no-one's documented it. Anyone got any ideas? Thanks! Seb Thirlway |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a PivotTable w/o selecting data in an existing PivotTable | Excel Discussion (Misc queries) | |||
Cannot copy PivotTable to Network, at work. | Excel Discussion (Misc queries) | |||
I inserted a field =Amount1 - amount2 -PivotTable-doesn't work | Excel Discussion (Misc queries) | |||
PivotTable - PivotTable Field name is not valid - error! | Excel Programming | |||
Creating a PivotTable Report from an Another PivotTable Report | Excel Programming |