CODEWITHSUNDEEP
vbaexcel
Jimmer
Jimmer

Reputation: 15

Excel Slicer Manipulation with VBA

I am learning how create and manipulate Excel slicers with VBA. I have been following some examples by Paul Te Braack on his "Business Intelligence Website: https://paultebraak.wordpress.com/2012/02/24/accessing-the-slicer-through-vba/

When I try to cycle through the items of a slicer cache and print the values of "caption", "value" and "name" in the immediate window, it returns an error of "Run-time error '1004': Application-defined or object-defined error for the line: " This is the code: "Set SL = sC.SlicerCacheLevels(1)"

Sub BTest()
Dim sC As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem

application.EnableEvents = False
application.Calculation = xlManual
Set sC = ActiveWorkbook.SlicerCaches(5)
Set SL = sC.SlicerCacheLevels(1)
Debug.Print “——————————————————————————”

For Each sI In SL.SlicerItems
Debug.Print “Caption; – > ” & sI.Caption
Debug.Print “Value; – > ” + CStr(sI.Value)
Debug.Print “Unique; Name; – > ” + sI.Name
Debug.Print “——————————————————————————”

Next
application.EnableEvents = True
application.Calculation = xlCalculationAutomatic

End Sub

I modified the code, but my modified code returns the results "False," "False," and "Sheet5False" are returned. The pivot table and the slicer are in Sheet5 by the way. (which I think should work based on the slicer cache and item verifications mentioned below.)

When I review the slicer cached in the locals window, it shows it has 30 items, and I can expand the items and display the values of the caption, value, and name.

When I type "Debug.Print sC.SlicerItems.Count" in the immediate window it returns an answer of 30. When I type "Debug.Print sC.SlicerItems(2).Name in the immediate window it returns the answer of 30.

The modified code is:

Sub CTest()
Dim sC As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem

application.EnableEvents = False
application.Calculation = xlManual
Set sC = ActiveWorkbook.SlicerCaches(5)
Set sI = sC.SlicerItems(1)
Debug.Print “——————————————————————————”

For Each sI In sC.SlicerItems
Debug.Print “Caption; – > ” & sI.Caption
Debug.Print “Value; – > ” + CStr(sI.Value)
Debug.Print “Unique; Name; – > ” + sI.Name
Debug.Print “——————————————————————————”

Next
application.EnableEvents = True
application.Calculation = xlCalculationAutomatic

End Sub

Any guidance on what I am doing wrong in the original code example or the modified code example will be

Upvotes: 0

Views: 2216

Answers (1)

DiegoAndresJAY
DiegoAndresJAY

Reputation: 706

SlicerItemLevels is only applicable to OLAP data feeds. See this reference.

It seems that the website you copied the code from used ASCII characters 147 (“) & 148 (”) for open and close quotes. VBA expects ASCII character 34 (") for both. The following should work just fine.

Sub CTest()
Dim sC As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem

Application.EnableEvents = False
Application.Calculation = xlManual
Set sC = ActiveWorkbook.SlicerCaches(5)
Set sI = sC.SlicerItems(1)
Debug.Print "——————————————————————————"

For Each sI In sC.SlicerItems
Debug.Print "Caption; – > " & sI.Caption
Debug.Print "Value; – > " + CStr(sI.Value)
Debug.Print "Unique; Name; – > " + sI.Name
Debug.Print "——————————————————————————"

Next
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

Upvotes: 1

Related Questions