• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

John Jairo V

Well-Known Member
Greetings to all of you!

I have this VBA code:

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!
 
Last edited by a moderator:

p45cal

Well-Known Member
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

Well-Known Member
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!
 
Top