Filter in pivottable

[linkav]

Beste,

Ik heb een excel met een pivottable en in vba doorloop ik de filters die gezet zijn in de pivottable. Als ik een filter verwijder dan blijft, wordt dit veld in vba nog altijd als filter zien.

Hoe komt dat? Hoe kan ik dit oplossen?

    For Each pvt_field In Worksheets(Par_Pivotsheet).PivotTables(Par_Pivot).PivotFields
        'MsgBox pvt_field.Name
        If pvt_field.Orientation = 1 Or _
            pvt_field.Orientation = 2 Or _
            pvt_field.Orientation = 3 Or pvt_field.CubeField.CubeFieldType = xlHierarchy Then '1 = rijveld  // 2 = Kolomveld // 3 = filter veld // 4 = [Measures].[# Subscribers]
            pvt_field_name = pvt_field.Name
            
           [B][COLOR="#000000"] Filter = Workbooks(gblWorkbookName).Sheets(Par_Pivotsheet).PivotTables(Par_Pivot).PivotFields(pvt_field_name).VisibleItemsList[/COLOR][/B]
            
            If Filter(1) <> "" Then
                If UBound(Filter) > 1 Then
                    For i = 1 To UBound(Filter)
                        tmpFilter = fnctReplaceQuote(Filter(i))
                        If i = 1 Then
                            statement_field = "{" & tmpFilter & ","
                        ElseIf i = UBound(Filter) Then
                            statement_field = statement_field & tmpFilter & "}"
                        Else
                            statement_field = statement_field & tmpFilter & ","
                        End If
                    Next
                ElseIf UBound(Filter) = 1 Then
                    tmpFilter = fnctReplaceQuote(Filter(1))
                    statement_field = "{" & tmpFilter & "}"
                End If
                ws.Cells(LRow, 1).Value = pvt_field.Name
                ws.Cells(LRow, 2).Value = pvt_field.Orientation
                ws.Cells(LRow, 3).Value = statement_field
                ws.Cells(LRow, 4).Value = Par_Pivotsheet
                RowTo = LRow
                LRow = LRow + 1
            End If
        End If
    Next pvt_field

 

[linkav]

Opgelost:

    For Each pvt_field In Worksheets(Par_Pivotsheet).PivotTables(Par_Pivot).PivotFields
        
        [COLOR="#FF0000"]If (pvt_field.Orientation = 1 Or _
            pvt_field.Orientation = 2 Or _
            pvt_field.Orientation = 3) And pvt_field.CubeField.CubeFieldType = xlHierarchy Then '1 = rijveld  // 2 = Kolomveld // 3 = filter veld // 4 = [Measures].[# Subscribers]
            pvt_field_name = pvt_field.Name[/COLOR]
            
            MsgBox pvt_field.Name
            MsgBox pvt_field.Orientation
            
            Filter = Workbooks(gblWorkbookName).Sheets(Par_Pivotsheet).PivotTables(Par_Pivot).PivotFields(pvt_field_name).VisibleItemsList
            
            If Filter(1) <> "" Then
                If UBound(Filter) > 1 Then
                    For i = 1 To UBound(Filter)
                        tmpFilter = fnctReplaceQuote(Filter(i))
                        If i = 1 Then
                            statement_field = "{" & tmpFilter & ","
                        ElseIf i = UBound(Filter) Then
                            statement_field = statement_field & tmpFilter & "}"
                        Else
                            statement_field = statement_field & tmpFilter & ","
                        End If
                    Next
                ElseIf UBound(Filter) = 1 Then
                    tmpFilter = fnctReplaceQuote(Filter(1))
                    statement_field = "{" & tmpFilter & "}"
                End If
                ws.Cells(LRow, 1).Value = pvt_field.Name
                ws.Cells(LRow, 2).Value = pvt_field.Orientation
                ws.Cells(LRow, 3).Value = statement_field
                ws.Cells(LRow, 4).Value = Par_Pivotsheet
                RowTo = LRow
                LRow = LRow + 1
            End If
        End If
    Next pvt_field

 

[snb]

Vermijd namen van variabelen die tot verwarring met VBA of Excel kunnen leiden; bijv. gebruik v_filter in plaats van filter.

 

[linkav]

OK ga ik op letten!