确定Excel切片器指向的字段

Question

我正试图编写一个宏，以便当用户在枢轴表上钻取工作簿时，该钻取就会将相关的切片过滤器应用到钻取结果中(为什么以上帝的名义微软还没有这样做，这超出了我的能力)。

所讨论的工作簿有两个基于独立数据的枢轴表，我已经制定了(大多数)逻辑来检查是否有任何切片器应用了过滤器，如果是，那么选择哪个值(S)。我也有关于Before_DoubleClick & Worksheet_Activate操作的逻辑，它让宏知道正在进行钻取(因为我以前使用过代码，并且知道它可以工作)。

最后一步是找到切片器基于的列，并从钻取表中删除行，其中列的值未被选中。我以为我会在记录切片器的创建的基础上寻找SlicerCache.Levels.SourceFiled，但是当我试图编写代码(至少它没有显示在AutoComplete选项中)时，它似乎是不可用的。

我的当前代码实际上是在以下几个方面过滤该演练：

Sub Filter_Drill_Down(SourceSheet As Worksheet, Drill_Down_Sheet As Worksheet)
'
Dim Slicer_Column As String
Dim Selected_Values As String
Dim Selected_Cnt As Long
Dim Total_Values As Long
Dim All_Slicers As SlicerCaches
Dim SCache As SlicerCache
Dim Slicer_Obj As Slicer
Dim S_Item As SlicerItem
Dim DDS_Row As Long
Dim No_Match As Boolean

Application.ScreenUpdating = False

'Capture all the slicers in the workbook (since they're not natively sheet specific)
Set All_Slicers = ThisWorkbook.SlicerCaches

'Iterate through the slicer caches
For Each SCache In All_Slicers
    'Check if the current cache has a Slicer Object on the source sheet
    For Each Slicer_Obj In SCache.Slicers
        If Slicer_Obj.Shape.Parent Is SourceSheet Then

            'In order to prevent wasting time applying filters for slicers where no filtering occurs, we'll first check how many items are selected. Reset the counters to make
            'sure we don't miscount, and clear Selected_Values so we don't include incorrect values
            Selected_Cnt = 0
            Total_Values = 0
            Selected_Values = ""

            For Each S_Item In SCache.SlicerItems
                If S_Item.Selected Then
                    'Current item is selected, increment the selected count & add the value to the selected values string. That way if we do need to filter we can just check if
                    ' a row's value for the relevant column is in the string & delete rows where that's not true
                    Selected_Cnt = Selected_Cnt + 1

                    Selected_Values = Selected_Values & "|" & S_Item.Name & "|"
                End If 'else the item isn't selected, just need to increment the total count which happens anyway

                Total_Values = Total_Values + 1
            Next

            'Check if the total = selected
            If Total_Values > Selected_Cnt Then
                'We actually need to filter. The first step in that is knowing which column to check
                Slicer_Column = "A"
                No_Match = True

                'Find the matching column
                Do While Drill_Down_Sheet.Range(Slicer_Column & 1).Value <> "" And No_Match
                    'Check if the header for the current column is the one we're looking for
                    If Drill_Down_Sheet.Range(Slicer_Column & 1).Value = <X> Then'HERE'S WHERE I NEED HELP
                        No_Match = False
                    Else
                        'Move to the next column
                        Slicer_Column = ColNumToStr(ColStrToNum(Slicer_Column) + 1) 'ColNumToStr converts a # to it's letter equivalent (i.e. 27 = AA) & ColStrToNum does the inverse
                    End If
                Loop

                'Iterate through the rows. Column A is ALWAYS filled, so use it to control the loop
                DDS_Row = 2
                Do While Drill_Down_Sheet.Range("A" & DDS_Row).Value <> ""
                    'Check the value in the target column against the selected items string
                    If InStr(1, Selected_Values, "|" & Drill_Down_Sheet.Range(Slicer_Column & DDS_Row).Value & "|") = 0 Then
                        'Value isn't selected, delete the row. Note: Since this will make what had been row DDS_Row + 1 into row DDS_Row, we do NOT increment DDS_Row
                        Drill_Down_Sheet.Range(DDS_Row & ":" & DDS_Row).Delete xlShiftUp


                    Else
                        'Not a filtered value, move to the next row
                        DDS_Row = DDS_Row + 1
                    End If
                Loop

            End If 'all the values are selected, which means none are filtered, so do nothing
        End If 'else the slicer does not appear on the Source_Sheet, meaning it's not one that applies to the pivot table being drilled into. Just move to the next cache
    Next
Next

Application.ScreenUpdating = True
End Sub

Answer 1

一些蛮力的试验和错误给了我答案。您应该使用SlicerCache.SourceName来获取字段名。