使用VBA从数据透视图获取可见项列表

问题描述

我使用下面的代码来循环通过一个透视字段来获取可见的枢纽项。但是当枢轴字段位于行标签中时， pivotitem.count 给出了一个0。当我将这个枢轴字段移动到列标签时，代码正在工作精细。但我需要这个字段留在行标签。

I'm using the below code to loop through a pivot field for getting the pivot items which are visible. But the pivotitem.count gives a 0 when the pivot field is in Row Label When I move this pivot field to Column Label, the code is working fine. But I NEED this Field to stay on row label.

我的问题有任何工作吗？

Is there any work around for my problem?

Dim pt As PivotTable
Dim pf As PivotField
Dim pvtitem As PivotItem

Set nwSheet = Worksheets.Add
nwSheet.Activate
rw = 0

Set pt = Sheets("Reasons").PivotTables("PivotFields")
Set pf = pt.PivotFields("[Characteristics].[Reason].[Reason]")

With pf
    For i = 0 To .PivotItems.Count
        rw = rw + 1
        nwSheet.Cells(rw, 1).Value = .PivotItems.Count
    Next i
End With

推荐答案

迭代在RowFields ，显式地，可以获取行字段中可见的枢轴项的句柄。
请看这是否符合用途：

Iterating on RowFields, explicitly, can get a handle on visible Pivot Items in Row Fields.Please see if this serves the purpose:

    Set pt = Sheets("Reasons").PivotTables("PivotFields")
    Dim pf As PivotField
    For Each pf In pt.RowFields
          MsgBox pf.Name & " : " & pf.VisibleItems.Count
    Next

要迭代报表过滤器：

Dim pt As PivotTable
Dim pFilter As PivotFilter
Dim pFilters As PivotFilters
Dim pF As PivotField

Set pt = Sheets("Reasons").PivotTables("PivotFields")
'Set the first RowField as the PivotField
Set pF = pt.RowFields(1)

'Remove previous filters
pF.ClearAllFilters

'Assuming we have a RowField as 'Quarter' and DataField as 'Sum of Sale',
'we can apply a new 'ValueIsLessThan' filter for a Sum of Sale value of 12000

pt.PivotFields("Quarter").PivotFilters.Add Type:=xlValueIsLessThan, _
DataField:=pt.PivotFields("Sum of Sale"), Value1:=12000

Set pFilters = pt.PivotFields("Quarter").PivotFilters

For Each pFilter In pFilters
  MsgBox "Filter Applied On: " & pFilter.PivotField.Name & " -- Filtered values for less than : " & pFilter.Value1
Next

这篇关于使用VBA从数据透视图获取可见项列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！