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