无法设置PivotItem类（VBA）的Visible属性 [英] Unable to set the Visible property of the PivotItem class (VBA)

问题描述

我正在尝试操作一个Excel 2007数据透视表通过VBA，所以我可以循环的数据透视表的类别，全部设置为不可见但一个，保存为pdf格式并继续下一个类别。为此，我使用以下代码段。

  Dim pf As PivotField 
设置pf = ActiveSheet.PivotTables（ PivotTable1）PivotFields（NAME）
 
 Dim pi as PivotItem 
对于每个pi在pf.PivotItems 
 
如果pi.Visible = False然后
 pi.Visible = True'Error here 
 End If 
 
 Dim pi2 As PivotItem 
对于每个pi2在pf.PivotItems 
如果pi2& pi然后
 pi2.Visible = False 
结束如果
下一个pi2 
 
'保存到PDF在这里
下一个pi 
  

循环似乎是第一次工作。每个类别都被取消选择，但是第一个类别会输出一个不错的PDF文件。下一次它进入循环，但它会在指示的行上给出无法设置PivotItem类的Visible属性错误。我知道在一个数据透视表中，必须至少选择一个项目，但这并不是问题，因为我正在尝试将可见性设置为TRUE而不是FALSE。

我尝试通过对其进行检查来修复它，因为也许您不允许将已经可见的PivotItem设置为可见，但似乎不起作用。

任何帮助将非常感谢！

解决方案

这是由于使用缓存的枢轴项而不是当前的。确保表不保留任何旧项目。要这样做，右键单击您的数据透视表，单击数据选项卡，并将每个字段保留的itesm的数量设置为无。 VBA中的代码是：

  Dim pt As PivotTable 
 
 pt.PivotCache.MissingItemsLimit = xlMissingItemsNone 
  

I am trying to manipulate an Excel 2007 Pivot Table trough VBA so I can loop trough the categories of the pivot table, set all to invisible but one, save the sheet as pdf and continue to the next category. For this I use the following piece of code.

Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME")

Dim pi as PivotItem
For Each pi In pf.PivotItems

    If pi.Visible = False Then
        pi.Visible = True 'Error here
    End If

    Dim pi2 As PivotItem
    For Each pi2 In pf.PivotItems
        If pi2 <> pi Then
            pi2.Visible = False
        End If
    Next pi2

    'Saving to PDF goes here
Next pi

The loop seems to be working the first time. Every category gets deselected but the first and it outputs a nice PDF file. The next time it enters the loop however it gives an 'Unable to set the Visible property of the PivotItem class' error at the indicated line. I am aware of the fact that in a pivot table there has to be at least one item selected but that is not the problem here as I am trying to set the visibility to TRUE instead of FALSE.

I tried fixing it by putting a check around it as maybe you are not allowed to set an already visible PivotItem to visible but that did not seem to work.

Any help would be very much appreciated!

解决方案

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

这篇关于无法设置PivotItem类（VBA）的Visible属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！