VBA更改切片器项目选择

Question

我是 VBA 的新手，需要学习如何自动更改切片器上的选定值。 我首先尝试了一个非常简单的方法，但我尝试了以下代码的所有可能变体，并且总是收到错误 1004，这次是"应用程序定义或对象定义的错误">

Sub SlicerSelect()
With ActiveWorkbook.SlicerCaches("Slicer_Time")
.SlicerItems("2016").Selected = False
End With
End Sub

有人有想法吗？这也是我的切片器及其设置的图像。

顺便说一下，当我使用 .清除手动筛选器命令。

多谢！

这也是通过手动过滤我的项目的宏记录：

Sub Macro2()
' Macro2 Macro
ActiveWorkbook.SlicerCaches("Slicer_Time2").VisibleSlicerItemsList = Array( _
"[Booking Period].[Time].[YEAR].&[2018]")
End Sub

Answer 1

您的问题是有两种不同类型的数据透视表：

• 基于区域的数据透视表，使用您最初使用的代码类型 发布，这使您可以一次传入一个单独的数据透视项; 和
• 基于数据模型(即 PowerPivot(或 OLAP 多维数据集的数据透视表， 使用完全不同的语法，您必须传入 数组显示您希望可见的所有项目，使用更多 令人困惑的语法。

Answer 2

看起来您正在使用Microsoft表作为源数据。因此，请尝试以下示例。 当然，这可以是动态的，并且需要添加错误处理。但这是基于 Active工作簿中的Microsoft表数据源在现有切片器中选择一个切片器项的语法的基本要点。

Sub SlicerSelect()
Dim STRING_ as String
'Replace TableName, ColumnName, and Value with your specific scenario.
'Value will be the specific slicer item you want to select.
STRING_ = "[TableName].[ColumnName].&[Value]"
'Replace Slicer_Name with your own value
ActiveWorkbook.SlicerCaches("Slicer_Name").VisibleSlicerItemsList = Array(STRING_)
End Sub

Answer 3

筛选切片器项可能很棘手，因为至少有一个项必须始终保持可见。此代码演示如何在名为 vSelect 的数组上筛选切片器，并演示需要如何完成此操作。

Option Explicit
Sub FilterSlicer()
Dim slr As Slicer
Dim sc As SlicerCache
Dim si As SlicerItem
Dim i As Long
Dim vItem As Variant
Dim vSelection As Variant
Set sc = ActiveWorkbook.SlicerCaches("Slicer_ID")
'Set sc = slr.SlicerCache
vSelection = Array("B", "C", "E")
For Each pt In sc.PivotTables
pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed
Next pt
With sc
'At least one item must remain visible in the Slicer at all times, so make the first
'item visible, and at the end of the routine, check if it actually  *should* be visible
.SlicerItems(1).Selected = True
'Hide any other items that aren't already hidden.
'Note that it is far quicker to check the status than to change it.
' So only hide each item if it isn't already hidden
For i = 2 To .SlicerItems.Count
If .SlicerItems(i).Selected Then .SlicerItems(i).Selected = False
Next i
'Make the PivotItems of interest visible
On Error Resume Next 'In case one of the items isn't found
For Each vItem In vSelection
.SlicerItems(vItem).Selected = True
Next vItem
On Error GoTo 0
'Hide the first PivotItem, unless it is one of the countries of interest
On Error Resume Next
If InStr(UCase(Join(vSelection, "|")), UCase(.SlicerItems(1).Name)) = 0 Then .SlicerItems(1).Selected = False
If Err.Number <> 0 Then
.ClearAllFilters
MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Slicer, so I have cleared the filter"
End If
On Error GoTo 0
End With

For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
End Sub