Не удалось установить свойство Visible класса PivotItem (VBA)

Я пытаюсь манипулировать сводной таблицей Excel 2007 через VBA, чтобы я мог перебирать категории сводной таблицы, устанавливать все невидимые, кроме одной, сохранять лист в формате 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

Цикл, кажется, работает в первый раз. Каждая категория отменяется, кроме первой, и выводится хороший PDF-файл. Однако в следующий раз, когда он входит в цикл, он выдает ошибку «Невозможно установить свойство Visible класса PivotItem» в указанной строке. Я знаю, что в сводной таблице должен быть выбран хотя бы один элемент, но это не проблема, поскольку я пытаюсь установить для видимости значение TRUE вместо FALSE.

Я попытался исправить это, поставив галочку вокруг него, так как, возможно, вам не разрешено делать видимым уже видимый PivotItem, но это, похоже, не сработало.

Любая помощь будет очень высоко ценится!


excel pivot-table vba
person Stijnvdk    schedule 13.07.2012    source источник
comment
Если вы закомментируете раздел, который создает PDF, он будет работать без ошибок?   -  person Dick Kusleika    schedule 13.07.2012
comment
Какую версию Excel вы используете?   -  person RBarryYoung    schedule 13.07.2012

Ответы (5)


arrow_upward
8
arrow_downward

Это связано с тем, что сводная таблица использует кэшированные элементы сводной таблицы вместо текущей. Убедитесь, что на столе не осталось старых предметов. Для этого щелкните правой кнопкой мыши сводную таблицу, перейдите на вкладку «Данные» и установите «Количество элементов для сохранения в поле» на «Нет». Код для этого в VBA:

Dim pt As PivotTable

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
person Duc Pham    schedule 11.07.2013

arrow_upward
5
arrow_downward

Я понимаю, что это старо, но хотел внести свой вклад для тех, кто ищет решение в будущем.

Я столкнулся с той же ошибкой, решение, которое я придумал, состояло в том, чтобы просто обновить сводную таблицу перед началом цикла поворота.

Попробуйте следующую строку кода:

ActiveSheet.PivotTables("PivotTable1").RefreshTable
person whistler    schedule 09.04.2013
comment
Привет, Бен, добро пожаловать в Stack Overflow! +1 за конструктивный ответ. Не забудьте поместить весь код в блоки кода (просто поставьте 4 пробела перед кодом при вводе его в текстовое поле ответа) для удобочитаемости. - person Mike H.; 09.04.2013

arrow_upward
1
arrow_downward

Вы не можете сделать PivotItem невидимым, если нет других видимых PivotItem.

т.е. последнюю не спрячешь.

person AJ.    schedule 04.09.2019
comment
Вау, это первый раз, когда я опубликовал сообщение за ДОООООООООООООООООО времени! ;) - person AJ.; 04.09.2019

arrow_upward
0
arrow_downward

Проверьте ориентацию PivotItem. Я считаю, что вы не можете установить для Visible значение True, если ориентация xlHidden. Если это так, то сначала просто измените ориентацию на что-то другое.

person RBarryYoung    schedule 13.07.2012

arrow_upward
0
arrow_downward

Может быть одно из следующего:

  • Вам нужен хотя бы один видимый PivotItem, и вы делаете их невидимыми.
  • Ориентация PivotField == XlPivotFieldOrientation.xlHidden (0)
  • AutoSortOrder PivotField! = Constants.xlManual (-4135)

Ниже вы можете найти пример вспомогательных функций на 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)
    {

    }
}
person Michael    schedule 19.01.2017