无法获取pivotitem类的可见属性 [英] unable to get the visible property of the pivotitem class
问题描述
我有两组代码.由于某些原因，在第一个错误中，而在第二个错误中.
1:
Dim pi As PivotItem
Dim pf As PivotField
Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school")
For Each pi In pf.PivotItems
If pi = "(leeg)" Then
pi.Visible = False
Else
pi.Visible = True 'ERROR HERE
End If
Next pi
2:
Dim pi As PivotItem
Dim pf As PivotField
Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("naam locatie")
For Each pi In pf.PivotItems
If InStr(pi, "BSO") Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
我收到错误消息:无法获取ivotitem类的可见属性"
我读到我应该解决以下问题:
这是由于数据透视表使用了缓存的数据透视表项，而不是 当前的.确保表格中没有保留任何旧物品.到 这样做，右键单击数据透视表，单击数据"选项卡并设置 每个字段保留的项目数量"为无".这样做的代码 VBA是:
将pt设置为数据透视表
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
我试图通过两种方式添加此行代码:
Dim pi As PivotItem
Dim pf As PivotField
Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school")
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone '1st TRY
For Each pi In pf.PivotItems
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone '2nd TRY
If pi = "(leeg)" Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
这似乎无法解决我的问题.
1.您的行/列字段中是否有多个字段?
因为问题可能来自此.
PivotField中的所有PivotItems都不总是显示/显示，因为它们位于第二级，具体取决于第一级. 为避免由于错误而导致代码中断，您必须使用 错误处理程序.
只有在第一级中与相应的PivotItem一起找到的PivotItems才可显示(即，您无法显示未在数据中发生的情况).
例如，您不能在第二级显示PivotItem汽车"
当第一级PivotItem为运输方式飞行"时.
2.刷新PivotCache
话虽如此，您可以在使用设置MissingItemsLimit来确保拥有最新数据之后，立即刷新枢纽缓存(检查是否已将Pt定义为数据透视表):
Set Pt = Sheets("Afname per school").PivotTables("Draaitabel3")
Set pf = Pt.PivotFields("school")
Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Pt.PivotCache.Refresh
3.代码逻辑
之后再看您的代码，我有点困惑，因为您正在做的是隐藏由他的名字找到的特定PivotItem，但是您还尝试显示其他所有PivotItem！
我认为这是这里的主要问题，我建议一个没有参数和错误处理的例程，像这样:
Sub Hide_PivotItem(PivotTable_Object As PivotTable, _
PivotField_Name As String, _
ByVal PivotItem_Name As String, _
ByVal UnHide_All As Boolean)
Dim Pt As PivotTable, _
Pi As PivotItem, _
Pf As PivotField
Set Pt = PivotTable_Object
Set Pf = Pt.PivotFields(PivotField_Name)
Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Pt.PivotCache.Refresh
If UnHide_All Then
On Error Resume Next
For Each Pi In Pf.PivotItems
Pi.Visible = True
Next Pi
On Error GoTo 0
Else
'Don't unhide other items
End If
For Each Pi In Pf.PivotItems
If Pi.Name <> PivotItem_Name Then
Else
Pi.Visible = False
End If
Next Pi
End Sub
I have two sets of code. For some reason, in the first one I get an error, and in the second one I don't.
1:
Dim pi As PivotItem
Dim pf As PivotField
Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school")
For Each pi In pf.PivotItems
If pi = "(leeg)" Then
pi.Visible = False
Else
pi.Visible = True 'ERROR HERE
End If
Next pi
2:
Dim pi As PivotItem
Dim pf As PivotField
Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("naam locatie")
For Each pi In pf.PivotItems
If InStr(pi, "BSO") Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
I get the error: "unable to get the visible property of the pivotitem class"
I read I should solve this the following:
This is due to the Pivot table using the cached pivot items instead of the current one. Make sure the table does not retain any old items. To do so, right click on your pivot table, click on Data tab and set "Number of itesm to retain per field" to "None". The code to do so in VBA is:
Dim pt As PivotTable
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
I tried to add this line of code in two ways:
Dim pi As PivotItem
Dim pf As PivotField
Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school")
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone '1st TRY
For Each pi In pf.PivotItems
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone '2nd TRY
If pi = "(leeg)" Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
This doesn't seem to solve my problem.
1. Do you have multiples fields in your rows/columns fields?
Because the problem might be coming from this.
All the PivotItems in PivotField are not always displayed/displayable because they are in the second level, depending on the first level. To avoid code breaking because of the error, you'll have to use an Error Handler.
Only the PivotItems that are found with the corresponding PivotItem from the first level are displayable (IE you can't displayed a case that didn't happen in your data).
For instance you can't display the PivotItem "Car" at 2nd level
when the 1st level PivotItem is "Flying mode of transportation".
2. Refresh PivotCache
That being said, you could simply refresh the pivot cache (check that you have defined Pt as your PivotTable) right after using the setting MissingItemsLimit to be sure that you have the most recent data :
Set Pt = Sheets("Afname per school").PivotTables("Draaitabel3")
Set pf = Pt.PivotFields("school")
Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Pt.PivotCache.Refresh
3. Code logic
Looking at your code afterwards, I'm a bit perplex, because what you are doing is hiding a specific PivotItem found by his name, but you also try to display every other PivotItem!
And I think that is the main problem here, I would suggest a routine with few arguments and error handling, something like this :
Sub Hide_PivotItem(PivotTable_Object As PivotTable, _
PivotField_Name As String, _
ByVal PivotItem_Name As String, _
ByVal UnHide_All As Boolean)
Dim Pt As PivotTable, _
Pi As PivotItem, _
Pf As PivotField
Set Pt = PivotTable_Object
Set Pf = Pt.PivotFields(PivotField_Name)
Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Pt.PivotCache.Refresh
If UnHide_All Then
On Error Resume Next
For Each Pi In Pf.PivotItems
Pi.Visible = True
Next Pi
On Error GoTo 0
Else
'Don't unhide other items
End If
For Each Pi In Pf.PivotItems
If Pi.Name <> PivotItem_Name Then
Else
Pi.Visible = False
End If
Next Pi
End Sub
这篇关于无法获取pivotitem类的可见属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
