首页 文章

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

提问于
浏览
3

我试图通过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

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

我尝试通过检查它来修复它,因为可能你不允许将已经可见的PivotItem设置为可见,但这似乎不起作用 .

任何帮助将非常感谢!

4 回答

  • 0

    我意识到这已经过时了,但希望为那些寻求未来解决方案的人做出贡献 .

    我遇到了同样的错误,我提出的解决方案就是在开始你的pivotitem循环之前刷新pivottable .

    请尝试以下代码行:

    ActiveSheet.PivotTables("PivotTable1").RefreshTable
    
  • 0

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

  • 3

    可能有以下之一:

    • 您需要至少一个可见的PivotItem,并且您将它们全部设置为不可见

    • PivotField的Orientation == XlPivotFieldOrientation.xlHidden(0)

    • PivotField的AutoSortOrder!= 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)
        {
    
        }
    }
    
  • 6

    这是由于Pivot表使用缓存的透视项而不是当前的透视项 . 确保表格不保留任何旧项目 . 要执行此操作,请右键单击数据透视表,单击“数据”选项卡,然后将“每个字段保留的数量”设置为“无” . 在VBA中执行此操作的代码是:

    Dim pt As PivotTable
    
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    

相关问题