使用 VBA 遍历工作簿切片器名称

Loop through workbook slicer names using VBA

提问by Jay Killeen

I have tried Googling and searching for this one but just can't quite get it. All I am trying to do is loop through the slicers on an activeworksheet and delete the slicer if it exists.

我试过谷歌搜索并搜索这个，但不能完全得到它。我想要做的就是遍历活动工作表上的切片器，并删除切片器（如果存在）。

At the moment I have 6 slicers sitting there. Previously I had

目前我有 6 个切片机坐在那里。以前我有

    ActiveSheet.Shapes.Range(Array("Market Segment Name 2", "Line of Business 2" _
    , "Customer Name", "Product Group Name", "Product Type Name", "Product Code") _
    ).Select
    Selection.Delete

But this was no good if I had already deleted the slicers.

但是如果我已经删除了切片器，那就不好了。

Now I am trying (note wb is set as a global variable in a module named "Public")

现在我正在尝试（注意 wb 被设置为名为“Public”的模块中的全局变量）

Option Explicit
Dim sl As Slicer
Dim slName As String

Set wb = ActiveWorkbook

For Each sl In wb.SlicerCaches
    If sl.Name = "Market Segment Name 2" Or _
        sl.Name = "Line of Business 2" Or _
        sl.Name = "Customer Name" Or _
        sl.Name = "Product Group Name" Or _
        sl.Name = "Product Type Name" Or _
        sl.Name = "Product Name" Then
        slName = sl.Name
        ActiveSheet.Shapes.Range(slName).Delete
    End If
Next sl

To me it seems like it should work. I have gotten it to work if I go down to SlicerItem level but I just can't figure out how to access it at Slicer level...

对我来说，它似乎应该起作用。如果我进入 SlicerItem 级别，我就可以使用它，但是我无法弄清楚如何在 Slicer 级别访问它...

Any ideas would be greatly appreciated. Thank you.

任何想法将不胜感激。谢谢你。

If this fails I will have a go at building the array and deleting that way but I would still need a way of testing whether the slicer currently exists or not.

如果失败，我将尝试构建数组并以这种方式删除，但我仍然需要一种测试切片器当前是否存在的方法。

回答by Siddharth Rout

There are two ways that I can think of.

我能想到的有两种方法。

One is the Forcemethod. Here we don't check if the slicer exists. We simply delete it if it exists. For example

一是Force方法。这里我们不检查切片器是否存在。如果它存在，我们只需删除它。例如

On Error Resume Next
ActiveSheet.Shapes.Range("Market Segment Name 2").Delete
ActiveSheet.Shapes.Range("Line of Business 2").Delete
'
'~~> And so on
'
On Error GoTo 0

And the other method is to actually check if the slicer exists and then delete it. For example

另一种方法是实际检查切片器是否存在，然后将其删除。例如

Dim sl As SlicerCache

On Error Resume Next
Set sl = ActiveWorkbook.SlicerCaches("Market Segment Name 2")
On Error GoTo 0

If Not sl Is Nothing Then sl.Delete

'For Each sl In ActiveWorkbook.SlicerCaches
    'Debug.Print sl.Name
'Next

EDIT:

编辑：

Followup from comments.

从评论跟进。

Converting the first code into a loop.

将第一个代码转换为循环。

Sub Sample()
    Dim sSlicers As String
    Dim Myar
    Dim i As Long

    '~~> Slicers you want to delete
    sSlicers = "Market Segment Name 2,Line of Business 2"
    sSlicers = sSlicers & "," & "Customer Name,Product Group Name"
    sSlicers = sSlicers & "," & "Product Type Name,Product Code"

    '~~> Split the names using "," as a delimiter
    '~~> If your slicer names have "," then use a different delimiter
    Myar = Split(sSlicers, ",")

    For i = LBound(Myar) To UBound(Myar)
        On Error Resume Next
        ActiveSheet.Shapes.Range(Myar(i)).Delete
        On Error GoTo 0
    Next i
End Sub

回答by Jay Killeen

Here is another peice of code I have finally figured out to do what I was originally trying to achieve.

这是我最终想出的另一段代码来完成我最初想要实现的目标。

Sub LoopSlicerNames()

Dim slCaches As SlicerCaches
Dim slCache As SlicerCache

Set slCaches = ThisWorkbook.SlicerCaches

For Each slCache In slCaches

    'MsgBox (slCache.Name & " is used for " & slCache.PivotTables.Item(1).Name)
    If slCache.PivotTables.Item(1).Name = "PTDashboard" Then
        slCache.Delete
        'MsgBox ("Slicer has been deleted")
    End If

    'If slCache.Name = "Slicer_Market_Segment_Name2" Then slCache.Delete

Next slCache

End Sub

The difference with this one is that I targeted the slicer based on its parent pivottable being on the worksheet so I wouldn't need to add the exact name of the slicer into the code. If I did want to add the name of the slicer into the code I can just switch around the commenting.

与此不同的是，我根据工作表上的父数据透视表定位了切片器，因此我不需要将切片器的确切名称添加到代码中。如果我确实想将切片器的名称添加到代码中，我可以切换注释。

Thanks again Siddarth. You got me over the initial hurdles and provided good solutions. I think my initial problem was thinking that the slicer was the child of the slicercache. Looking into it now it seems to go slicerCaches, SlicerCache then SlicerItem and the 'slicer' as I referred to in my original question was the 'SlicerCache' level... so that picture I linked just confused the hell out of me haha.

再次感谢悉达多。你让我克服了最初的障碍并提供了很好的解决方案。我认为我最初的问题是认为切片器是切片器缓存的子代。现在查看它似乎是 slicerCaches，SlicerCache 然后 SlicerItem 和我在原始问题中提到的“切片器”是“SlicerCache”级别......所以我链接的那张图片只是让我感到困惑，哈哈。