Expand - Collapse multiple items of PivotTable Data Model with VBA Excel

[John Jairo V]

Greetings to all of you!

I have this VBA code:

Set pT = ActiveSheet.PivotTables("Liquidation")
Set pF = pT.PivotFields("[Vessel_LQ].[District].[District]")
 
For i = 1 To UBound(a)
  pF.PivotItems("[Vessel_LQ].[District].&[" & a(i) & "]").DrilledDown = False
Next i

Initially, all the items are expanded.
In vector a I have the names of the items that I want to collapse. This works fine when I collapse just one item, but when the loop collapse the second one, the first get expand. what is going on? How can I prevent this behaviour? There are a way to collapse two (or more items) without issues with VBA?

I appreciate your help. Blessings!

 

[p45cal]

1. Isn't collapse/expand .ShowDetail rather than .DrilledDown?
2. I note from the Help associated with PivotItem.DrilledDown: (a) 'Use this property only for OLAP data sources.' and (b) 'You cannot set this property if the field or item is hidden'
3. Is the syntax of:
pF.PivotItems("[Vessel_LQ].[District].&[" & a(i) & "]").DrilledDown = False
completely right? Is that ampersand in the right place; I'd have expected it to be absent, more like:
pF.PivotItems("[Vessel_LQ].[District].[" & a(i) & "]").DrilledDown = False
?

 

[John Jairo V]

Hi p45cal. Thx for your reply.

1. Isn't collapse/expand .ShowDetail rather than .DrilledDown?
No... just work .DrilledDown (For PivotTable Data Model)

2. I note from the Help associated with PivotItem.DrilledDown: (a) 'Use this property only for OLAP data sources.' and (b) 'You cannot set this property if the field or item is hidden'
Like I said, initially, all the items are expanded (not hidden in any form).

3. Is the syntax of:
pF.PivotItems("[Vessel_LQ].[District].&[" & a(i) & "]").DrilledDown = False
completely right? Is that ampersand in the right place; I'd have expected it to be absent, more like:
pF.PivotItems("[Vessel_LQ].[District].[" & a(i) & "]").DrilledDown = False
The sintaxis is correct (is just and extraction of macro recorder when collapse/expand some item).

Blessings!

 

[p45cal]

OK then, let's have a workbook with this happening so I can play…

 

[John Jairo V]

OK then, let's have a workbook with this happening so I can play…

Thx for your help. I'd prefer to avoid the grouping and use the hidden property of sheet instead. Blessings!

 

[p45cal]

OK