返回数据透视表单元格的行，列和页面字段和项目的VBA代码 [英] VBA Code to return pivot table cell's row, column, and page fields and items
问题描述
这似乎应该是相当直截了当的，因为当您将鼠标悬停在数据透视表中的任何单元格上时，它将显示在Contextures框中的向下钻取信息，但是我很难操作数据透视表对象因为在线或微软似乎没有太多的写作。
似乎枢轴线或枢轴线对象可能是我正在寻找的，但我无法弄清楚如何使用它。
我最初采取了这种方法，它工作正常，直到我意识到rowfields的索引不一定是在行字段中的位置，所以我不得不废除这个。
$ b $在子完成后，b Sub ActualDetailDrill（）
'，当双击数据透视表中的单元格时，调用此子。
Dim NumOfRowItems As Integer
Dim NumOfColItems As Integer
Dim NumOfPageFields As Integer
Dim Field As PivotField
Dim ActualDrillActiveCell As Range
Set ActualDrillActiveCell = Sheets（ActualDrill SQL Build）。Range（A1）
NumOfRowItems = ActiveCell.PivotCell.RowItems.Count
i = 1
直到我> NumOfRowItems
ActualDrillActiveCell.Value = ActiveCell.PivotTable.RowFields（i）.Name
ActualDrillActiveCell.Offset（0，1）.Value = ActiveCell.PivotCell.RowItems（i）.Name
ActualDrillActiveCell = ActualDrillActiveCell.Offset（1，0）
i = i + 1
循环
End Sub
任何帮助将非常非常感谢。这是我正在开展的一个大项目的最后一个步骤之一，将对我所在的公司非常有帮助。
这是周末，我有一段时间来挖掘这个有趣的问题。我认为你使用 RowItems 和 ColumnItems 非常接近。 PivotTable.RowFields 更为通用，但不适用于 PivotCell 级别。
我讨厌使用Page Fields，但认为这是正确的逻辑：
Sub GetValueFieldStuff（）
Dim pvtCell As Excel.PivotCell
Dim pvtTable As Excel.PivotTable
Dim pvtField As Excel.PivotField
Dim pvtItem As Excel.PivotItem
Dim pvtParentItem作为Excel.PivotField
Dim i As Long
On Error Resume Next
设置pvtCell = ActiveCell.PivotCell
如果Err.Number<> 0然后
MsgBox游标需要在一个数据透视表
退出子
结束如果
错误GoTo 0
如果pvtCell.PivotCellType <> xlPivotCellValue然后
MsgBox游标需要在值字段单元格
退出子
结束如果
设置pvtTable = pvtCell.PivotTable
对于每个pvtField在pvtTable.PageFields
i = 0
对于每个pvtItem在pvtField.PivotItems
如果pvtItem.Visible然后
i = i + 1
Debug.PrintPageField &安培; pvtField.Name& - 数据透视表&我& 是& pvtItem.Name
End If
下一个pvtItem
下一个pvtField
Debug.PrintValue Field Name is& pvtCell.PivotField.Name
Debug.PrintValue Field Source is& pvtCell.PivotField.SourceName
For i = 1 To pvtCell.RowItems.Count
设置pvtParentItem = pvtCell.RowItems（i）.Parent
Debug.PrintRow Item& ;我& 是& pvtCell.RowItems（i）.Name& 父系列字段是：& pvtParentItem.Name
Next i
对于i = 1到pvtCell.ColumnItems.Count
设置pvtParentItem = pvtCell.ColumnItems（i）.Parent
Debug.Print栏目&我& 是& pvtCell.ColumnItems（ⅰ）请将.Name; 这是父列字段是：& pvtParentItem.Name
Next i
End Sub
如果您还没有，考虑使用VBE的本地窗口。在数据透视表的对象模型中进行钻取（和备份）非常好。这就是我看到一个 ColumnItem 是一个 PivotItem ，其 Parent 是 PivotField 它在。
I'm trying to write some code that will return the row, column, and page fields and items after I've highlighted a cell in the values area of the pivot table. For example, if I select a cell that contains a value of $1000, I'd like to the code to pull the row field and item (for example, the field would be Department and item would be Finance), column field and item (for example, the field would be Account and item would be Advertising) and page field and item (for example, the field would be Company and item would be XYZ, Inc.).
This seems like it should be pretty straightforward, because when you hover over any cell in a pivot table, it will show you the drilldown information in the contextures box, however, I'm having a hard time manipulating the pivot table objects because there doesn't seem to be much written on them online or by microsoft.
It seems like the pivotline or pivotlines object might be what I'm looking for, but I can't figure out how to use it.
I originally took this approach and it worked fine until I realized that a rowfields' index is not necessarily it's position in the row field, so I had to scrap this.
Sub ActualDetailDrill()
'after sub is completed, make this sub called when a cell in a pivot table is double-clicked.
Dim NumOfRowItems As Integer
Dim NumOfColItems As Integer
Dim NumOfPageFields As Integer
Dim Field As PivotField
Dim ActualDrillActiveCell As Range
Set ActualDrillActiveCell = Sheets("ActualDrill SQL Build").Range("A1")
NumOfRowItems = ActiveCell.PivotCell.RowItems.Count
i = 1
Do Until i > NumOfRowItems
ActualDrillActiveCell.Value = ActiveCell.PivotTable.RowFields(i).Name
ActualDrillActiveCell.Offset(0, 1).Value = ActiveCell.PivotCell.RowItems(i).Name
ActualDrillActiveCell = ActualDrillActiveCell.Offset(1, 0)
i = i + 1
Loop
End Sub
Any help would be very, very appreciated. This is one of the last steps in a big project I'm working on that will be very helpful to the company I work for.
It's the weekend and I had some time to dig into this interesting question. I think you were pretty close by using RowItems and ColumnItems. The PivotTable.RowFields are more general though and don't apply at the PivotCell level.
I hate working with Page Fields, but think this is the correct logic for that:
Sub GetValueFieldStuff()
Dim pvtCell As Excel.PivotCell
Dim pvtTable As Excel.PivotTable
Dim pvtField As Excel.PivotField
Dim pvtItem As Excel.PivotItem
Dim pvtParentItem As Excel.PivotField
Dim i As Long
On Error Resume Next
Set pvtCell = ActiveCell.PivotCell
If Err.Number <> 0 Then
MsgBox "The cursor needs to be in a pivot table"
Exit Sub
End If
On Error GoTo 0
If pvtCell.PivotCellType <> xlPivotCellValue Then
MsgBox "The cursor needs to be in a Value field cell"
Exit Sub
End If
Set pvtTable = pvtCell.PivotTable
For Each pvtField In pvtTable.PageFields
i = 0
For Each pvtItem In pvtField.PivotItems
If pvtItem.Visible Then
i = i + 1
Debug.Print "PageField " & pvtField.Name & " - Pivot Item " & i & " is " & pvtItem.Name
End If
Next pvtItem
Next pvtField
Debug.Print "Value Field Name is " & pvtCell.PivotField.Name
Debug.Print "Value Field Source is " & pvtCell.PivotField.SourceName
For i = 1 To pvtCell.RowItems.Count
Set pvtParentItem = pvtCell.RowItems(i).Parent
Debug.Print "Row Item " & i & " is " & pvtCell.RowItems(i).Name & ". It's parent Row Field is: " & pvtParentItem.Name
Next i
For i = 1 To pvtCell.ColumnItems.Count
Set pvtParentItem = pvtCell.ColumnItems(i).Parent
Debug.Print "Column Item " & i & " is " & pvtCell.ColumnItems(i).Name; ". It's parent Column Field is: " & pvtParentItem.Name
Next i
End Sub
If you aren't already, consider using the VBE's Locals Window. It's great for drilling down (and back up) in the pivot table's object model. That's how I saw that a ColumnItemis a PivotItem whoseParent is the PivotField it's in.
这篇关于返回数据透视表单元格的行，列和页面字段和项目的VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
