excel - 过滤具有随机项目范围的数据透视表（连接到外部源）

Question

我有一个随机项目列表，我想在Excel pivot table. 数据透视表连接到外部数据源。有什么方法可以过滤数据透视表中的整个列表，而无需一次将一个项目添加到过滤器中？

我在 VBA 中尝试了以下代码，它适用于本地数据源，但不适用于外部数据源。

“56607016”、“84000110”、“8A20371”是我列表中的一些项目示例。

如果这里有比 VBA 更好的解决方案，请告诉我。

Sub FilterPivotItems()

Dim PT          As PivotTable
Dim PTItm       As PivotItem
Dim FiterArr()  As Variant

' use an array to select the items in the pivot filter you want to keep visible
FiterArr = Array("56607016", "84000110", "8A20371")

' set the Pivot Table
Set PT = ActiveSheet.PivotTables("PivotTable2")

' loop through all Pivot Items in "Product number" Pivot field
For Each PTItm In PT.PivotFields("[Released products].[Product number].[Product number]").PivotItems
    If Not IsError(Application.Match(PTItm.Caption, FiterArr, 0)) Then ' check if current item is not in the filter array
        PTItm.Visible = True
    Else
        PTItm.Visible = False
    End If
Next PTItm

End Sub

我在运行时收到的错误消息：

“运行时错误‘1004’：无法获取 PivotItems 类的 _NewEnum 属性”

标签： excelvbapivot-tableexternal-data-source

Answer 1

而不是使用 For Each 循环，将 FilterArr 更改为枢轴引用，然后您可以使用将值设置.VisibleItemsList为您的 FilterArr。这是一个例子。

FilterArr = Array( _
        "[myTableName].[myPivotField].&[myPivotItem1]", _
        "[myTableName].[myPivotField].&[myPivotItem2]")


    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[myTableName].[myPivotField].[myPivotField]").VisibleItemsList = FilterArr

此示例假定您在同一个工作簿中有一个工作表，其中一个表包含应过滤数据透视表的值。每个过滤器值的透视过滤器字符串使用相邻列中的公式创建，然后读入数组。使用命名范围中的值创建其他变量。

Sub FilterPivot_WithListOfValues()


'---------------------------------------------------------------------------------------------------------
'   Purpose:    Dynamically create an array based on the values of one column of a table.
'
'   Customize:  Inputs in this sub come from 6 named ranges on a worksheet in this workbook.
'               myPivotTableName, myPivotFieldName, mySheetName,
'               myFilterTableName, smyDataTableName, myFilterCol
'
'   Revisions:
'   09/30/19    Sub created
'
'---------------------------------------------------------------------------------------------------------


'~~~> Set the data types for the variables.
Dim oPT As PivotTable
Dim oPF As PivotField
Dim oPI As PivotItem
Dim strPT As String 'pivot table name
Dim strPF As String 'pivot field name
Dim strWS As String 'pivot table sheet name
Dim oWS As Worksheet
Dim oFTable As ListObject
Dim oDTable As ListObject
Dim strFTable As String 'filter table name
Dim strDTable As String 'data table name
Dim strFilterString As String 'pivot table filter string
Dim FilterArr() As Variant
Dim TempArray
Dim i As Long
Dim iCol As Long

'~~~> Assign the variables.
strPT = [myPivotTableName]
strPF = [myPivotFieldName]
strWS = [mySheetName]
strFTable = [myFilterTableName]
strDTable = [myDataTableName]
iCol = [myFilterCol]

'~~~> Build the filter string.  Don't include the quotation marks wrapper.
'     That is automatically added by .PivotFields
strFilterString = "[" & strDTable & "].[" & strPF & "].[" & strPF & "]"

'~~~> Check the variables in the Immediate Window.
Debug.Print "Variable Set: strPT = " & strPT
Debug.Print "Variable Set: strPF = " & strPF
Debug.Print "Variable Set: strWS = " & strWS
Debug.Print "Variable Set: strDTable = " & strDTable
Debug.Print "Variable Set: strFTable = " & strFTable
Debug.Print "Variable Set: iCol = " & iCol
Debug.Print "Variable Set: strFilterString = " & strFilterString


    '~~~> Set the path for the table variable
    Set oFTable = Range(strFTable).ListObject

    '~~~> Set the path for the sheet variable
    Set oWS = Worksheets(strWS)

    '~~~> Create an array list from a table column.
    TempArray = oFTable.DataBodyRange.Columns(iCol)

    '~~~> Convert from vertical array to horizontal array list.
    FilterArr = Application.Transpose(TempArray)


DisplayArrayValues:
'~~~> Loop through each item in the table array and display in Immediate Window [ctrl + g]

    For i = LBound(FilterArr) To UBound(FilterArr)

        Debug.Print FilterArr(i)

    Next i


ApplyPivotFilters:
'~~~> Apply filters to the pivot table.

    Set oPF = oWS.PivotTables(strPT).PivotFields(strFilterString)

    With oPF

        .VisibleItemsList = FilterArr

    End With


ReleaseVariables:
'~~~> Release the variables from memory.

Set oPT = Nothing
Set oPF = Nothing
Set oPI = Nothing
Set oWS = Nothing
Set oFTable = Nothing
Set oDTable = Nothing
strPT = vbNullString
strPF = vbNullString
strWS = vbNullString
strFTable = vbNullString
strDTable = vbNullString
strFilterString = vbNullString
i = vbNull
iCol = vbNull

End Sub

这是过滤器表和命名范围的片段。

这是用于生成过滤器字符串的公式。

="["&myDataTableName&"].["&myPivotFieldName&"].&["&[@[Pivot Items]]&"]"