Return the number of objects in a slicer from another workbook

Question

I am simply trying to get the number of objects in a slicer from another workbook.

Let's suppose I'm working on excel's workbook A.

I'm using the following code :

Sub slicerCount()

Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(CurDir & "\" & Dir(CurDir & "\" & "*B*.xlsm"))

If wb Is Nothing Then
    Set wb = Workbooks.Open(CurDir & "\" & Dir(CurDir & "\" & "*B*.xlsm"))
End If

wb.Activate
Set slBox = wb.Sheets("Sheet").SlicerCaches("ID")
number = slBox.SlicerItems.Count

End Sub

Unfortunately, number remains empty. I tried to returns cells value from the same sheet, it works but it doesn't work with slicers.

Could you help me please?

Regards

ouroboros1 · Accepted Answer · 2025-01-25 20:06:23Z

TL;DR: SlicerCaches is a property of the Workbook object.

Sub slicer_Count()

For Each sc In ActiveWorkbook.SlicerCaches

    Debug.Print (sc.SlicerItems.Count)

Next

End Sub

Your use of On Error Resume Next make the code pass over in silence the error for line:

Set slBox = wb.Sheets("Sheet").SlicerCaches("ID")

Error:

Run-time error '438':

Object doesn't support this property or method

That is a reference to wb.Sheets("Sheet"), a Worksheet object. See the documentation for SlicerCache:

Use the SlicerCaches property of the Workbook object to access the SlicerCaches collection of SlicerCache objects in a workbook.

I.e., instead of wb.Sheets("Sheet") you need:

Set slBox = wb.SlicerCaches("Slicer_ID") 'or less likely: "ID"
number = slBox.SlicerItems.Count

Note that, by default, a slicer name will be assigned like "Slicer_[Column_Name]". E.g., a slicer for column "Col A" will be named "Slicer_Col_A", and one for column "ID" will be named "Slicer_ID".

When you have slicers for similarly named columns from separate tables, excel will auto-increment: "Slicer_ID", "Slicer_ID1", etc.

Of course, you can change the name by setting slBox.Name = "ID". So, "ID" might be correct, but "Slicer_ID" seems more likely.

If you are not sure, you can either print the names in vba, or find the name of a specific slicer by selecting it, right-clicking it, and selecting "Slider Settings":

Collectives™ on Stack Overflow

Return the number of objects in a slicer from another workbook

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related