vba Power 数据透视表 - 循环浏览报表筛选器中的字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18785789/
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
Power Pivot Table - Looping through Fields in Report Filters
提问by Siddharth Rout
Disclaimer: This question was posted in msdn forumbut since I didn't get any reply (surprisingly), I am cross posting it here as well. You might want to check that link before you try to attempt this question. I wouldn't want you to waste your time if a solution was posted in that link :)
免责声明：此问题已发布在msdn 论坛中，但由于我没有得到任何回复（令人惊讶的是），我也在这里交叉发布。在尝试尝试此问题之前，您可能需要检查该链接。如果在该链接中发布了解决方案，我不希望您浪费时间:)
Problem: I want to find out the field names in the Report Filter. So I am looking for RegionCountryName, Citynameand ProductKey. The Report Filters are dynamic and can change.
问题：我想找出报表过滤器中的字段名称。所以我正在寻找RegionCountryName,Cityname和ProductKey。报告过滤器是动态的并且可以改变。
Screenshot:
截图：
What have I tried: I have checked every property of the Power Pivot but there is no property that I could find which would let me loop through the fields of the power pivot.
我尝试了什么：我已经检查了 Power Pivot 的每个属性，但是我找不到可以让我遍历 Power Pivot 字段的属性。
I have even tried this code but it apparently give me an error on Set pf = pt.PageFields(1)
我什至尝试过这段代码，但它显然给了我一个错误 Set pf = pt.PageFields(1)
Sub Sample()
Dim pt As PivotTable, pi As PivotItem, pf As PivotField
Dim lLoop As Long
Set pt = Sheet1.PivotTables(1)
Set pf = pt.PageFields(1)
For Each pi In pf.PivotItems
Debug.Print pi.Name
Next pi
End Sub
Error ScreenShot
错误截图
So what am I missing? Or is there a different approach that I should take?
那么我错过了什么？或者我应该采取不同的方法吗？
EDIT
编辑
If someone wants to experiment with it, the file can be downloaded from HEREI am looking at the InventorySheet.
如果有人想试验它，可以从这里下载该文件，我正在查看工作Inventory表。
回答by flaberenne
After some testing, it seems that you can get the list of Page filter fields by listing the PivotFields and checking if the value of their Orientation property is equal to XlPageField. Try the code below :
经过一些测试，您似乎可以通过列出 PivotFields 并检查它们的 Orientation 属性的值是否等于 XlPageField 来获取页面过滤器字段的列表。试试下面的代码：
Sub Test()
Dim pt as PivotTable
Dim pf as PivotFields
set pt=Sheets("test").PivotTables(1)
For each pf in pt.PivotFields
If pf.Orientation=xlPageField Then
Debug.Print pf.Name
End If
Next pf
End Sub
Hope this helps
希望这可以帮助
回答by crx
If you change pt.pivotfields to pt.pagefields in the post of realce_net it should run. As the Microsoft reference says, pagefields was introduced with Office 2013.
如果您在 realce_net 的帖子中将 pt.pivotfields 更改为 pt.pagefields，它应该会运行。正如 Microsoft 参考所述，页面字段是在 Office 2013 中引入的。
Dim pt As PivotTable
Dim pf As PivotFields
Set pt = Sheets("test").PivotTables(1)
For Each pf In pt.PageFields
If pf.Orientation = xlPageField Then
Debug.Print pf.Name
End If
Next pf
回答by EndreK
More than 2 years have passed since the original question has been raised but I still could not find a satisfying answer...
自从最初的问题提出以来已经过去了2年多，但我仍然找不到令人满意的答案......
BUT I found a way around :-) You can change PowerPivot table via slicers too.
但是我找到了一种解决方法:-) 您也可以通过切片器更改 PowerPivot 表。
I used this code to change the selected month in my file:
我使用此代码更改文件中选定的月份：
Private Sub SpinButton21_SpinDown()
Dim SC As SlicerCache
Dim SL As SlicerCacheLevel
Dim SI As SlicerItem
Set SC = ActiveWorkbook.SlicerCaches("Slicer_Month4")
Set SL = SC.SlicerCacheLevels(1)
'get the current item number
c = 1
For Each SI In SL.SlicerItems
If SI.Selected = True Then
MONTHindex = c
Exit For
End If
c = c + 1
Next SI
If MONTHindex = 1 Then
MONTHindex = SC.SlicerCacheLevels.Item.Count + 1
End If
SC.VisibleSlicerItemsList = SL.SlicerItems(MONTHindex - 1).Name
End Sub
I am not a pro but I hope it helps.
我不是专业人士，但我希望它有所帮助。
