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

Question

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!

Answer 1

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

Answer 2

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

Answer 3

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

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

Answer 4

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.

Answer 5

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)

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

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)
    {

    }
}