vba 获取特定单元格的数据透视字段的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21444344/
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
Get value of pivotfields for a particular cell
提问by Kyle Weller
I have a pivot table from ssas connection. Several pivotfields are being used in the table. For this example let's say they are Size, Color, and Location. My goal is to write some vba where I pass the cell range and get the value of the corresponding pivotfields.
我有一个来自 ssas 连接的数据透视表。表中使用了几个数据透视字段。在这个例子中，假设它们是大小、颜色和位置。我的目标是编写一些 vba，在其中传递单元格范围并获取相应数据透视字段的值。
For example, If I pass the cell range for B6I want to know that that cell is from location2 in color2 for size1. If I pass range B8I get location1, Color3, Size1 etc.
例如，如果我传递单元格范围，B6我想知道该单元格来自 size1 的 color2 中的 location2。如果我通过范围，B8我会得到 location1、Color3、Size1 等。
Here is what I can do so far. I can get the value of the formula for that cell. Here is the result for B6: GETPIVOTDATA("[Measures].[myValues]",$A$1,"[d size].[size]","[d size].[size].&[Size1]","[d color].[color]","[d color].[color].&[Color2]","[d location].[location]","[d location].[location].&[location2]")with this code:
这是我目前能做的。我可以获得该单元格的公式值。这是 B6 的结果：GETPIVOTDATA("[Measures].[myValues]",$A$1,"[d size].[size]","[d size].[size].&[Size1]","[d color].[color]","[d color].[color].&[Color2]","[d location].[location]","[d location].[location].&[location2]")使用以下代码：
Function xxx(ByVal xyz As Range) As Variant
xxx = xyz.Formula
End Function
Sub blah()
MsgBox (xxx(ActiveWorkbook.Sheets("Sheet1").Range("D13")))
End Sub
I can also loop through the pivotfields and get their names, (but I don't know how to get their values) [Measures].[myValues], [d size].[size]etc... with this code:
我还可以遍历透视字段，并得到他们的名字，（但我不知道如何让他们的价值观）[Measures].[myValues]，[d size].[size]等等......用这样的代码：
With Worksheets("Sheet1").PivotTables(1)
For i = 1 To .PivotFields.Count
MsgBox .PivotFields(i).Name
Next
End With
My question is now, how do I get the value of those pivotfields for a particular cell. I would like to be able to send B6and get back, or have access to: location2, Color2, Size1
我现在的问题是，如何获取特定单元格的这些数据透视字段的值。我希望能够发送B6和返回，或有权访问：location2, Color2,Size1
Edit:
编辑：
Getting closer... I can now loop through each field and get their names and values like this [d size].[size]&[Size1]etc, but now I want to be able to get just the value of Size1or [Size1]:
越来越近......我现在可以遍历每个字段并获取它们的名称和值，例如这样[d size].[size]&[Size1]，但现在我希望能够获得Size1or的值[Size1]：
Sub Test()
Dim iRange As Variant
iRange = ActiveCell.Address
FieldPicker (iRange)
End Sub
Function FieldPicker(targetCell As Variant) As Variant
With Range(targetCell).PivotCell
For i = 1 To .RowItems.Count
MsgBox .RowItems(i).SourceName
Next
End With
End Function
回答by Tim Williams
Adapted from RoryA's answer here: http://www.mrexcel.com/forum/excel-questions/332678-pivottable-problem.html
改编自 RoryA 的回答：http://www.mrexcel.com/forum/excel-questions/332678-pivottable-problem.html
'Given a pivottable value cell and a category name, return the category value
'Note: you would need morte code to account for a "page" category
Function PivotCategoryValue(rngInput As Range, fldName As String) As String
Dim pc As PivotCell
Dim pf As PivotField, pi As PivotItem
Dim rv As String
On Error Resume Next
Set pc = rngInput.PivotCell
On Error GoTo err_handle
If pc Is Nothing Then
rv = "Not a pivot cell"
Else
Select Case pc.PivotCellType
Case xlPivotCellValue
If pc.RowItems.Count Then
For Each pi In pc.RowItems
If pi.Parent.Name = fldName Then
rv = pi.Value
GoTo done
End If
Next pi
End If
If pc.ColumnItems.Count Then
For Each pi In pc.ColumnItems
If pi.Parent.Name = fldName Then
rv = pi.Value
GoTo done
End If
Next pi
End If
Case Else
rv = "Not a pivot data cell"
End Select
End If
done:
PivotCategoryValue = rv
Exit Function
err_handle:
PivotCategoryValue = "Unknown error"
End Function
