无法设置 PivotItem 类 (VBA) 的 Visible 属性

Unable to set the Visible property of the PivotItem class (VBA)

提问by Stijnvdk

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.

我正在尝试通过 VBA 操作 Excel 2007 数据透视表，以便我可以遍历数据透视表的类别，将所有设置为不可见，但有一个，将工作表另存为 pdf 并继续下一个类别。为此，我使用以下代码段。

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.

循环似乎是第一次工作。除了第一个类别之外，每个类别都被取消选择，并输出一个不错的 PDF 文件。下次它进入循环时，它会在指示的行给出“无法设置 PivotItem 类的可见性属性”错误。我知道在数据透视表中必须至少选择一个项目，但这不是问题，因为我试图将可见性设置为 TRUE 而不是 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.

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

Any help would be very much appreciated!

任何帮助将不胜感激！

回答by Duc Pham

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:

这是因为数据透视表使用缓存的数据透视表而不是当前的数据透视表。确保桌子上没有保留任何旧物品。为此，右键单击数据透视表，单击数据选项卡并将“每个字段保留的项目数”设置为“无”。在 VBA 中这样做的代码是：

Dim pt As PivotTable

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

回答by whistler

I realize this is old, but wanted to contribute for those looking for a solution in the future.

我意识到这已经过时了，但希望为将来寻找解决方案的人做出贡献。

I experienced this same error, solution I came up with was to just refresh the pivottable before beginning your pivotitem loop.

我遇到了同样的错误，我想出的解决方案是在开始数据透视项循环之前刷新数据透视表。

Try the following line of code:

尝试以下代码行：

ActiveSheet.PivotTables("PivotTable1").RefreshTable

回答by AJ.

You can't make a PivotItem invisible if there are no other visible PivotItems.

如果没有其他可见的 PivotItem，则不能使 PivotItem 不可见。

I.e. you can't hide the last one.

即你不能隐藏最后一个。

回答by RBarryYoung

Check the PivotItem's Orientation. I believe that you cannot set Visible to True if the Orientation is xlHidden. If that's it, then just change the Orientation to something else first.

检查 PivotItem 的方向。我相信如果 Orientation 是 xlHidden，你不能将 Visible 设置为 True。如果是这样，那么只需先将方向更改为其他内容。

回答by Michael

There might be one of the following:

可能存在以下情况之一：

• You need at least one visible PivotItem and you're setting them all to invisible
• PivotField's Orientation == XlPivotFieldOrientation.xlHidden (0)
• PivotField's AutoSortOrder != Constants.xlManual (-4135)

• 您至少需要一个可见的 PivotItem 并且您将它们全部设置为不可见
• PivotField 的方向 == XlPivotFieldOrientation.xlHidden (0)
• PivotField 的 AutoSortOrder != Constants.xlManual (-4135)

You can find below an example in C# of helper functions for filtering a pivot field by specific pivot items:

您可以在 C# 中的帮助函数中找到以下示例，用于按特定数据透视项过滤数据透视字段：

public static void FilterPivotItems(PivotField pf, List<string> pivotItemNames)
{
    PivotItems pis = pf.ChildItems;

    // Orientation != XlPivotFieldOrientation.xlHidden and we need to filter by at least one value (as Excel implies)
    if (pf.Orientation != 0 && pivotItemNames.Count > 0)
    {
        int oldAutoSortOrder = 0;

        if (pf.AutoSortOrder != (int)Constants.xlManual)
        {
            oldAutoSortOrder = pf.AutoSortOrder;
            pf.AutoSort((int)Constants.xlManual, pf.Name);
        }

        int pivotItemsCount = pf.PivotItems().Count;
        List<int> pivotItemsToHide = new List<int>();

        for (int i = 1; i <= pivotItemsCount; i++)
        {
            PivotItem pi = pf.PivotItems(i);

            // check if current pivot item needs to be hidden (if it exists in pivotItemNames)
            var match = pivotItemNames.FirstOrDefault(stringToCheck => stringToCheck.Equals(pi.Value));

            if (match == null)
            {
                // hide these pivot items later because we can hit exception "Unable to set the Visible property of the PivotItem class"
                // (this happens because all pivot items get hidden and we need to have at least one visible)
                pivotItemsToHide.Add(i);
            }
            else
            {
                TryFilterPivotItems(pi, true, true);
            }
        }

        for (int i = 0; i < pivotItemsToHide.Count; i++)
        {
            PivotItem pi = pf.PivotItems(pivotItemsToHide[i]);
            TryFilterPivotItems(pi, false, true);
        }

        if (oldAutoSortOrder != 0)
        {
            pf.AutoSort(oldAutoSortOrder, pf.Name);
        }

        PivotTable pt = pf.Parent as PivotTable;
        if (pt != null)
        {
            pt.Update();
        }
    }
}

public static void TryFilterPivotItems(PivotItem currentPI, bool filterValue, bool deferLayoutUpdate = false)
{
    try
    {
        PivotField pf = currentPI.Parent;
        PivotTable pt = pf.Parent as PivotTable;

        if (currentPI.Visible != filterValue)
        {
            if (deferLayoutUpdate == true && pt != null)
            {
                // just keep these three lines stick together, no if, no nothing (otherwise ManualUpdate will reset back to false)
                pt.ManualUpdate = true;
                currentPI.Visible = filterValue;

                // this may be redundant since setting Visible property of pivot item, resets ManualUpdate to false
                pt.ManualUpdate = false;
            }
            else
            {
                currentPI.Visible = filterValue;
            }
        }
    }
    catch (Exception ex)
    {

    }
}

public static void TryFilterPivotItems(PivotField pf, string itemValue, bool filterValue, bool deferLayoutUpdate = false)
{
    try
    {
        PivotItem currentPI = pf.PivotItems(itemValue);
        TryFilterPivotItems(currentPI, filterValue, deferLayoutUpdate);
    }
    catch (Exception ex)
    {

    }
}