尝试在 Excel 中使用 VBA 从数据透视表中提取 DataField
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18575505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trying to pull DataField from PivotTable using VBA in Excel
提问by Pete Carter
I am trying to pull both a column value and a data value out of a pivot table, using Excel.
我正在尝试使用 Excel 从数据透视表中提取列值和数据值。
For j = 1 To 1 'pt.RowFields(i).PivotItems.Count
sum = pt.DataFields(2).PivotItems(j)
Client = pt.RowFields(1).PivotItems(j)
Sheets("InvoiceTemplate").Range("c7").Value = Client
Sheets("InvoiceTemplate").Range("i13").Value = sum
Next
The Client value comes back exactly as expected. However, the sum value throws the error:
Client 值完全按预期返回。但是，总和值会引发错误：
Unable To Get PivotItems Property from PivotField class.
With a PivotTable that looks like the below, I would expect results of Contoso and ￡60.50
使用如下所示的数据透视表，我希望 Contoso 和 ￡60.50 的结果
net gross
Contoso ￡50.00 ￡60.50
Adventureworks ￡100 ￡110.00
Any idea as to what I am doing wrong? By the way, I know I have a 1 TO 1 loop, but this will be modified once this is working.
知道我做错了什么吗？顺便说一下，我知道我有一个 1 TO 1 循环，但是一旦它起作用，就会修改它。
采纳答案by Doug Glancy
I think this does what you want:
我认为这可以满足您的要求：
Sub PrintPivotFields()
Dim pvt As Excel.PivotTable
Dim pvtAcctField As Excel.PivotField
Dim pvtNetField As Excel.PivotField
Dim pvtItem As Excel.PivotItem
Set pvt = ActiveCell.PivotTable
Set pvtAcctField = pvt.RowFields(1)
Set pvtNetField = pvt.DataFields(1)
For Each pvtItem In pvtAcctField.PivotItems
Debug.Print pvtItem.Name
Debug.Print Intersect(pvtItem.DataRange.EntireRow, pvtNetField.DataRange).Value
Next pvtItem
End Sub
I didn't refer to this great VBA pivot table referenceby Jon Peltier, but if you do you may find a better way.
我没有参考Jon Peltier 的这个伟大的 VBA 数据透视表参考，但如果你这样做了，你可能会找到更好的方法。
