使用VBA选择和取消选择多个切片器项目(OLAP数据) [英] Use VBA to select and deselect multiple slicer items (OLAP data)

问题描述

我正在处理一个仅选择所需切片器项目的脚本.我尝试使用.SlicerItems.Selected = True / False进行选择和取消选择，但是我使用的是OLAP数据源，在这种情况下.Selected是只读的.切片器项目的格式为YYYYWW，因此2018年的第7周为201807.

I am working on a script which selects only the needed slicer items. I tried using .SlicerItems.Selected = True / False for selecting and deselecting but I am using an OLAP data source in which case .Selected is read-only. The slicer items are in the format of YYYYWW so 7th week of 2018 would be 201807.

我录制了一个宏，选择了一些切片器项目，这就是它给我的:

I recorded a macro selecting some slicer items and this is what it gave me:

Sub Macro2()
    ActiveWorkbook.SlicerCaches("Slicer_YYYYWW").VisibleSlicerItemsList = Array( _
        "[Results].[YYYYWW].&[201726]", "[Results].[YYYYWW].&[201727]", _
        "[Results].[YYYYWW].&[201728]", "[Results].[YYYYWW].&[201729]", _
        "[Results].[YYYYWW].&[201730]", "[Results].[YYYYWW].&[201731]", _
        "[Results].[YYYYWW].&[201732]", "[Results].[YYYYWW].&[201733]", _
        "[Results].[YYYYWW].&[201734]", "[Results].[YYYYWW].&[201735]", _
        "[Results].[YYYYWW].&[201736]", "[Results].[YYYYWW].&[201737]", _
        "[Results].[YYYYWW].&[201738]", "[Results].[YYYYWW].&[201739]", _
        "[Results].[YYYYWW].&[201740]", "[Results].[YYYYWW].&[201741]", _
        "[Results].[YYYYWW].&[201742]", "[Results].[YYYYWW].&[201743]", _
        "[Results].[YYYYWW].&[201744]", "[Results].[YYYYWW].&[201745]", _
        "[Results].[YYYYWW].&[201746]", "[Results].[YYYYWW].&[201747]", _
        "[Results].[YYYYWW].&[201748]", "[Results].[YYYYWW].&[201749]", _
        "[Results].[YYYYWW].&[201750]", "[Results].[YYYYWW].&[201751]", _
        "[Results].[YYYYWW].&[201801]", "[Results].[YYYYWW].&[201802]", _
        "[Results].[YYYYWW].&[201803]")
End Sub

所以我尝试遵循此模板并创建一个类似的数组.这是我所走的路:

So I tried following this template and create an array like that. This is how far I have gotten:

Sub arrayTest()

Dim startDate As Long
    Dim endDate As Long
    Dim n As Long
    Dim i As Long
    Dim strN As String
    Dim sl As SlicerItem
    Dim strArr As Variant
    Dim dur As Long
    Dim result As String

    endDate = Range("C17").Value ' endDate is the last SlicerItem to be selected
    startDate = Range("G17").Value ' startDate is the first SlicerItem to be selected
    dur = Range("C19").Value ' duration is the the number of SlicerItems to be selected
    i = 0
    ReDim strArr(dur) As Variant
    With ActiveWorkbook.SlicerCaches("Slicer_YYYYWW")
'            .ClearManualFilter
        For n = startDate To endDate
            strN = CStr(n) ' convert n to string
            If n = 201753 Then ' this is needed for when the year changes
                strN = CStr(201801)
                n = 201801
            End If
            strArr(i) = """[Results].[YYYYWW].&[" & strN & "]""" ' write string into array
            i = i + 1

'                For Each sl In .SlicerCacheLevels(1).SlicerItems
'                    If sl.Name = strN Then
'                        sl.Selected = True
'                    Else
'                        sl.Selected = False ' this is read-only for OLAP data so it's not working
'                    End If
'                Next

        Next
        MsgBox Join(strArr, ", ") ' the MsgBox returns the correct string to be applied to select the right slicer items

        .VisibleSlicerItemsList = Join(strArr, ", ") ' Error 13: Type mismatch
    End With

End Sub

当前，该代码给出错误13:.VisibleSlicerItemsList = Join(strArr, ", ")上的类型不匹配，也对此进行了注释.因此，我猜测将strArr设置为Variant是错误的，数据未正确插入strArr或无法通过这种方式进行.如果是最新的，我该怎么办?

Currently, the code gives Error 13: Type mismatch on .VisibleSlicerItemsList = Join(strArr, ", "), which is also commented. So I'm guessing that either dimensioning strArr as Variant is wrong, the data is not inserted correctly into strArr or it's just impossible to do it this way. In the case of the latest one, how should I do it?

第29-35行注释掉的部分不起作用，因为它给出了sl.Selected = False上由应用程序定义或对象定义的错误(1004)的常见错误.

The part commented out on lines 29-35 does not work as it gives the usual error of Application-defined or object-defined error (1004) on sl.Selected = False.

推荐答案

我有一个类似的问题需要克服.我使用以下代码解决了这个问题:

I had a similar issue to overcome. Which I resolved using the following code:

Sub show_SlicerItems()

Dim sc As SlicerCache
Dim sL As SlicerCacheLevel
Dim si As SlicerItem
Dim slicerItems_Array()
Dim i As Long

Application.ScreenUpdating = False

    Set sc = ActiveWorkbook.SlicerCaches("Slicer_Name")
    Set sL = sc.SlicerCacheLevels(1)

    ActiveWorkbook.SlicerCaches("Slicer_Name").ClearManualFilter

    i = 0

    For Each si In sL.SlicerItems
        ReDim Preserve slicerItems_Array(i)

        If si.Value <> 0 Then
            slicerItems_Array(i) = si.Name
            i = i + 1
        End If
    Next

sc.VisibleSlicerItemsList = Array(slicerItems_Array)

Application.ScreenUpdating = True
End Sub

这篇关于使用VBA选择和取消选择多个切片器项目(OLAP数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！