How to list Excel Slicer Items in VBA

Question

I have Win10 and Excel 2016. This is is really perplexing to me. I have a number of Slicers in an Excel workbook file. In my test code, all I wanted to do are (1) Get a count of the Slicer items in the Slicer, but I got a

Run-time error '1004' Application-defined or object-defined error:

Debug.Print ActiveWorkbook.SlicerCaches("Slicer_BusinessDivision").SlicerItems.count

(2) I want to list out all the Slicer items using the below code, in this case also got the '1004' error:

Dim oSlicerItem       As slicerItem
      
For Each oSlicerItem In ActiveWorkbook.SlicerCaches("Slicer_BusinessDivision").SlicerItems
    Debug.Print oSlicerItem.Value
Next

According to the MS documentation, SlicerItems property is contained in a SlicerCache object in: https://docs.microsoft.com/en-us/office/vba/api/excel.sliceritems

which is what I specified and also Intellisense showed it as well. Interesting, when I Dim the object variable as above, Excel turn As SlicerItem to the lower case As slicerItem. Can you share your thoughts. I have been looking at the MS documentation and I don't know what I am doing wrong.

From: Dim oSlicerItem As SlicerItem

To: Dim oSlicerItem As slicerItem

After making adjustment based on BigBen and Tim's comment, I looked at the documentation, which said:

https://docs.microsoft.com/en-us/office/vba/api/excel.slicercachelevel

"Use the SlicerCacheLevels collection of the parent SlicerCache object to access the SlicerCacheLevel objects that represent these hierarchy levels."

Properties

Application
Count
Creator
CrossFilterType
Name
Ordinal
Parent
SlicerItems
SortItems
VisibleSlicerItemsList

which contains the SlicerItems collection, but not sure how I can access this. I tried using:

Dim cache             As SlicerCache

Set cache = ActiveWorkbook.SlicerCaches("Slicer_BusinessDivision")
Debug.Print cache.OLAP = True  '//this is true
   
Debug.Print cache.SlicerCacheLevels.Parent

But Intellisense does not show anything else beyond ".Parent", any ideas?

Thanks in advance,

G.

Try splitting that into steps: i.e. DIm cache As SlicerCache, Set cache = ActiveWorkbook.SlicerCaches("Slicer_BusinessDivision"). If that succeeds, then the issue is with SlicerItems. — BigBen
Ben, I am still getting it the '1004' error at the FOR EACH line. Dim cache As SlicerCache Set cache = ActiveWorkbook.SlicerCaches("Slicer_BusinessDivision") For Each oSlicerItem In cache.SlicerItems Debug.Print oSlicerItem.Value Next — Gerry
"Attempting to access the SlicerItems property for slicers that are connected to an external OLAP data source (SlicerCache.OLAP = True) generates a run-time error. For OLAP data sources, use the SlicerItems property of the SlicerCacheLevel object instead." — Tim Williams
Edit your post to add the updated code, its too hard to read in the comments — Toddleson

Gerry Gerry · Accepted Answer · 2021-05-07T21:19:53

I want to thank you both, Ben and Tim, for pointing me in the right direction regarding OLAP. I totally forgot that it was point to it. Additionally, I like to share my code snippet that worked:

Sub test()

   Dim oSlicerItem       As SlicerItem
   Dim cache             As SlicerCache


   Set cache = ActiveWorkbook.SlicerCaches("Slicer_BusinessDivision")
   Debug.Print cache.OLAP   '//True
    
    
   Debug.Print cache.SlicerCacheLevels(1).SlicerItems.Count
   For Each oSlicerItem In cache.SlicerCacheLevels(1).SlicerItems
      Debug.Print oSlicerItem.Value
   Next

End Sub

I had to use the index 1 because there's only one level and lo and behold, it cycled through all the items in the Slicer. Thank you again.

G

How to list Excel Slicer Items in VBA

1 Answers