使用VBA循环使用工作簿切片器名称 [英] Loop through workbook slicer names using VBA

问题描述

我已经尝试过Google搜索并搜索这个，但是不能完全了解它。所有我想要做的是循环通过活动工作表上的切片器，并删除切片器（如果存在）。

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.

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

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

    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.

现在我我尝试（注意wb被设置为名为公共的模块中的全局变量）

Now I am trying (note wb is set as a global variable in a module named "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

对我来说似乎应该有效。如果我去了SlicerItem级别，我已经得到了工作，但是我无法弄清楚如何在Slicer级别访问它...

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...

任何想法都会不胜感激。谢谢。

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.

推荐答案

有两种方法可以考虑。

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

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

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

编辑：

从评论中追踪

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

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

这篇关于使用VBA循环使用工作簿切片器名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！