• The forum (and all other resources in Chandoo.org) will be under maintenance and will be unavailable for a maximum of two hours on the first week of October 2023. The exact date, time and duration of the maintenance are subject to change. We regret the inconvience it may cause.

  • 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

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:
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
?
 
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!
 
You must log in or register to reply here.
Back
Top