使用宏在 Excel 2010 VBA 中创建数据透视表的过滤问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11162183/
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
Filtering Issues with Macro for Creating Pivot Table in Excel 2010 VBA
提问by user1475657
I am new to VBA and am trying to write a macro that will create a pivot table. I need to filter various fields and have tried using PivotFilters.Add and PivotItems to only let certain things through...sometimes it works, but other times it throws errors. The following code works just fine:
我是 VBA 新手，正在尝试编写一个宏来创建数据透视表。我需要过滤各种字段并尝试使用 PivotFilters.Add 和 PivotItems 只让某些东西通过......有时它可以工作，但有时它会引发错误。下面的代码工作得很好：
Sub CreatePivot()
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("CP Monthly Data").Select
Range("A1").Select
Set objTable = Sheet1.PivotTableWizard
objTable.Name = "Resource Requests"
objTable.InGridDropZones = True
objTable.RowAxisLayout xlTabularRow
Set objField = objTable.PivotFields("Company name")
objField.Orientation = xlRowField
objField.Position = 1
Set objField = objTable.PivotFields("Probability Status")
objField.Orientation = xlRowField
objField.Position = 2
objField.PivotItems("X - Lost - 0%").Visible = False
objField.PivotItems("X - On Hold - 0%").Visible = False
objField.AutoSort xlDescending, "Probability Status"
Set objField = objTable.PivotFields("Project")
objField.Orientation = xlRowField
objField.Position = 3
Set objField = objTable.PivotFields("Project manager")
objField.Orientation = xlRowField
objField.Position = 4
Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.AutoSort xlAscending, "Resource name"
Set objField = objTable.PivotFields("June, 2012")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "##"
objField.Caption = "June"
Set objField = objTable.PivotFields("Workgroup Name")
objField.Orientation = xlPageField
objField.PivotItems("ATG").Visible = False
objField.PivotItems("India - ATG").Visible = False
objField.PivotItems("India - Managed Middleware").Visible = False
Application.DisplayAlerts = True
End Sub
The "Resource name" field is giving me problems. I need to only show the resource names that begin with "*TBD" and exclude those that contain "ATG" in the name. I have so far tried the following:
“资源名称”字段给我带来了问题。我只需要显示以“*TBD”开头的资源名称，并排除名称中包含“ATG”的资源名称。到目前为止，我已经尝试了以下方法：
Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.PivotFilters.Add xlCaptionContains, Value1:="TBD"
objField.PivotFilters.Add xlCaptionDoesNotContain, Value1:="ATG"
objField.AutoSort xlAscending, "Resource name"
Which returns "Run-time error '1004': Application-defined or object-defined error
返回“运行时错误‘1004’：应用程序定义或对象定义错误
This isn't exactly what I need since I also need to filter out those without "TBD" in the name, but I have also tried:
这并不是我真正需要的，因为我还需要过滤掉名称中没有“TBD”的那些，但我也尝试过：
Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.PivotItems("*ATG*").Visible = False
objField.AutoSort xlAscending, "Resource name"
Which returns "Run-time error '1004': Unable to get the PivotItems property of the PivotField class
返回“运行时错误‘1004’：无法获取 PivotField 类的 PivotItems 属性
I have also tried recording a macro and checking the results against my code. The results use PivotFilters.Add which I tried. The main difference between the recorded macro and my code is the use of PivotTableWizard and I'm starting to wonder if that matters...I'm new, remember?
我也尝试过录制宏并根据我的代码检查结果。结果使用我尝试过的 PivotFilters.Add 。录制的宏和我的代码之间的主要区别是使用 PivotTableWizard，我开始怀疑这是否重要...我是新手，还记得吗？
Any ideas on how to fix this? I'm using Excel 2010 and have spent hours searching for this, and nothing I've tried has worked. Thanks in advance for any help!!
有想法该怎么解决这个吗？我正在使用 Excel 2010 并花了数小时搜索此内容，但我尝试过的任何方法都没有奏效。在此先感谢您的帮助！！
回答by Scott Holtzman
Unfortunatley, you cannot apply to of the same type of filters to one field in a PT. This linkis a decent read on that.
不幸的是，您不能将相同类型的过滤器应用于 PT 中的一个字段。这个链接是一个不错的阅读。
To solve your problem you can can replace this block of code in your post above:
要解决您的问题，您可以在上面的帖子中替换此代码块：
Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.AutoSort xlAscending, "Resource name"
With the below code:
使用以下代码：
'1) Filter on any thing that contains `TBD`
Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.PivotFilters.Add xlCaptionContains, Value1:="TBD"
'2) Loop through the items in the field and uncheck anything with `ATG` in the name
Dim pi As PivotItem
Dim i as Integer
For i = 1 To objField.PivotItems.Count
If InStr(1, objField.PivotItems(i), "ATG") <> 0 Then
objField.PivotItems(i).Visible = False
End If
Next
objField.AutoSort xlAscending, "Resource name"
