Excel切片器选择失效问题：从文件名提取区域名并筛选

我有一个包含Area名称的数据透视表，需要通过-连字符分隔符从文件名中提取区域名称，并用该名称选择切片器项。目前文件名提取功能正常，但切片器选择功能失效。

原代码如下：

Option Explicit

Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Spl
End Sub

Public Function Spl()
    Dim wbName As String
    Dim fileName As String
    'Get the workbook name
    wbName = ActiveWorkbook.Name
    'Split the workbook name by the dash character
    fileName = Split(wbName, "-")(0)
    'Call the subroutine for each slicer cache
    SelectSlicerItems "Slicer_Area", fileName
    
End Function

Sub SelectSlicerItems(slicerName As String, fileName As String)
    Dim sc As SlicerCache
    Dim si As SlicerItem
    Dim index As Integer
    'Set the slicer cache object
    Set sc = ActiveWorkbook.SlicerCaches(slicerName)
    'Loop through the slicer items
    For index = 1 To sc.SlicerCacheLevels.Count
        For Each si In sc.SlicerCacheLevels(index).SlicerItems
            If si.Name = fileName Then
                si.Selected = True
            Else
                si.Selected = False
            End If
        Next si
    Next index
End Sub

解决方案 #

由于数据模型限制，直接遍历修改切片器项的Selected属性会失效，需改用VisibleSlicerItemsList属性指定可见项。修改后的代码如下：

Sub sliceit()
'
' sliceit Macro
'
'
    Dim wbName As String
    Dim fileName As String
    'Get the workbook name
    wbName = ActiveWorkbook.Name
    'Split the workbook name by the dash character
    fileName = Split(wbName, "-")(0)
    'Call the subroutine for each slicer cache
    
    ActiveWorkbook.SlicerCaches("Slicer_Area").VisibleSlicerItemsList = Array( _
        "[Range].[Area].&[" + fileName + "]")
    ActiveWorkbook.SlicerCaches("Slicer_Area1").VisibleSlicerItemsList = Array( _
        "[Table2].[Area].&[" + fileName + "]")
    
End Sub

内容的提问来源于stack exchange，提问作者Mahbub Alam