通过VBA迭代切片机，每次选择一个物品 [英] Iterate over slicer via VBA and select a single item each time

问题描述

我在传播者中有几个切片机。我想通过VBA能够循环遍历其中一个，并逐个选择每个选项。
下面的宏似乎对我疲惫的眼睛很好，但是当我运行它时显然不起作用。当我在下面的BREAKPOINT标签中添加断点时，第一个项目被选中，但是当第一个项目被选中时，宏会继续进行，而最终选择了所有的项目。

 子切片器（slName As String）
 Dim slItem As SlicerItem，slDummy As SlicerItem 
 Dim slBox As SlicerCache 
设置slBox = ActiveWorkbook.SlicerCaches（slName）
对于每个slItem在slBox .SlicerItems 
对于每个slDummy在slBox .SlicerItems 
 slDummy.Selected =（slDummy.Name = slItem.Name）
下一个slDummy 
下一个slItem'BREAKPOINT 
 End Sub 
 
子测试（）
调用分片器（A_slicer_name）
 End Sub 
  

感谢您的帮助

编辑：

正如Scott Holtzman所指出的，结果是在选择一个新项目时，我只需要清除过滤器（slBox.ClearManualFilter）。

为什么这是必需的，而布尔测试在调试时正常工作？

以下代码正常工作：

 子切片器（slName As String）
 Dim slItem As SlicerItem， slDummy As SlicerItem 
 Dim slBox As SlicerCache 
设置slBox = ActiveWorkbook.SlicerCaches（slName）
对于每个slItem在slBox .SlicerItems 
 slBox.ClearManualFilter这是我需要的线ADD 
对于每个slDummy在slBox .SlicerItems 
 slDummy.Selected =（slDummy.Name = slItem.Name）
下一个slDummy 
下一个slItem 
 End Sub 
 
子测试（）
调用分片器（A_slicer_name）
结束子
  

解决方案

由于我在评论中链接的问题没有一个可接受的答案（用户永远不会选择回答或回复我的建议），我将提供解决方案到你的公关

 子切片（slName As String）
 
 Dim slItem As SlicerItem，slDummy作为SlicerItem 
 Dim slBox As SlicerCache 
 
设置slBox = ActiveWorkbook.SlicerCaches（slName）
 
'循环遍历每个切片器项
对于每个slItem在slBox .SlicerItems 
 
'显示所有项目开始
 slBox.ShowAllItems'或.ClearManualFilter 
 
'测试每个项目对自己
对于每个slDummy在slBox .SlicerItems 
 
'如果该项目等于第一个循环中的项目，则选择它
'否则不显示它（因此在嵌套循环之间显示1个）
如果slItem.Name = slDummy.Name然后slDummy.Selected = True否则：slDummy.Selected = False 
 
'更多的代码来处理数据（我怀疑）
 
下一个slDummy 
 
下一个slItem 
 
 End Sub 
  

I have several slicers within a spreadhseet. I'd like to be able to loop over one of these via VBA, and select every option one by one. The macro below seems fine to my tired eyes, but it obviously doesn't work when I run it. When I add a breakpoint at the 'BREAKPOINT tag here below, the first item gets selected, but then the macro goes to the second one while keeping the first one selected and I end up with all my items selected...

    Sub slicers(slName As String)
    Dim slItem As SlicerItem, slDummy As SlicerItem
    Dim slBox As SlicerCache
    Set slBox = ActiveWorkbook.SlicerCaches(slName)
    For Each slItem In slBox .SlicerItems
      For Each slDummy In slBox .SlicerItems
          slDummy.Selected = (slDummy.Name = slItem.Name)
      Next slDummy
    Next slItem 'BREAKPOINT
    End Sub

    Sub test()
    Call slicers("A_slicer_name")
    End Sub

Thanks for your help

EDIT:
As pointed out by Scott Holtzman, turns out that I just need to clear the filter (slBox.ClearManualFilter) upon selecting a new item.

Why is that necessary whereas the boolean test works properly when I debug.print it?

The code below works fine:

    Sub slicers(slName As String)
    Dim slItem As SlicerItem, slDummy As SlicerItem
    Dim slBox As SlicerCache
    Set slBox = ActiveWorkbook.SlicerCaches(slName)
    For Each slItem In slBox .SlicerItems
      slBox.ClearManualFilter 'THIS IS THE LINE I NEEDED TO ADD
      For Each slDummy In slBox .SlicerItems
          slDummy.Selected = (slDummy.Name = slItem.Name)
      Next slDummy
    Next slItem
    End Sub

    Sub test()
    Call slicers("A_slicer_name")
    End Sub

解决方案

Since the question I linked you too in my comment does not have an accepted answer (the user never choose to answer or reply to my suggestion), I will provide the solution to your problem here as well.

Sub slicers(slName As String)

    Dim slItem As SlicerItem, slDummy As SlicerItem
    Dim slBox As SlicerCache

    Set slBox = ActiveWorkbook.SlicerCaches(slName)

    'loop through each slicer item
    For Each slItem In slBox.SlicerItems

        'show all items to start
        slBox.ShowAllItems 'or .ClearManualFilter

        'test each item against itself
        For Each slDummy In slBox.SlicerItems

            'if the item equals the item in the first loop, then select it
            'otherwise don't show it (thus showing 1 at a time between the nested loops)
            If slItem.Name = slDummy.Name Then slDummy.Selected = True Else: slDummy.Selected = False

            'more code to process the data (I suspect)

        Next slDummy

    Next slItem

End Sub

这篇关于通过VBA迭代切片机，每次选择一个物品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！