时间戳

首页 > 解决方案 > 尝试选择 OLAP 数据透视字段数组元素时出现运行时错误 1004

excel - 尝试选择 OLAP 数据透视字段数组元素时出现运行时错误 1004

问题描述

我在循环浏览我的 OLAP 数据透视字段中的项目时遇到问题。我有一个数组，其中包含我希望过滤器循环遍历的名称/项目，但我无法让枢轴字段选择一个数组元素（我一次只希望选择一个）

那里有很多类似问题的问题，例如this 和this，我试图将编码合并到我的代码中。但是我似乎无法让它为我工作

这是我目前的代码。我尝试使用 currentpage 而不是 visibleitemlist，而不是 element，只使用 i 或行业数组（i）。

Sub Test()

Dim IndustryArray() As Variant
Dim element As Variant

Dim Industry As Range
Dim Data As Variant

Dim ptITS As PivotTable
Dim pfITS As PivotField
Dim ppfITS As PivotField

Set ptITS = Worksheets("Industry Time Series").PivotTables("PivotTable1")
Set pfITS = ptITS.PivotFields("[All Industries Combined].[Industry Details].[Industry Details]")
Set ppfITS = ptITS.PageFields("[All Industries Combined].[Industry Details].[Industry Details]")

Set Industry = Worksheets("Industry List").Range("A2:A211")
ReDim IndustryArray(0 To Industry.Cells.Count + 1)

For i = 0 To Industry.Cells.Count + 1
    IndustryArray(i) = "[ All Industries Combined].[Industry Details].&[" & Industry.Cells(i + 1).Value & "]"
Next i

For Each element In IndustryArray
    pfITS.ClearAllFilters
    pfITS.VisibleItemsList = element <-- error (for different variations as well)
Next element

End Sub

我希望过滤器选择数组的每个项目，然后清除过滤器并移至下一个项目。只需在已识别的行处出错。

任何帮助，将不胜感激！

标签： excelvbaloopspowerpivot

解决方案

虽然我没有 VBA 示例，但我希望这段C# 代码能帮助您找到代码中的问题。寻找workerFilterList_DoWork方法。相关代码为：

            Excel.CubeField field = pvt.CubeFields.get_Item(args.LookIn);
            field.CreatePivotFields();
            field.IncludeNewItemsInFilter = false; //if this is set to true, they essentially wanted to show everything but what was specifically unchecked. With Filter List, we're doing the reverse... showing only what's spefically checked

            foreach (string sLevelUniqueName in dictLevelsOfFoundMembers.Keys)
            {
                Excel.PivotField pivotField = (Excel.PivotField)field.PivotFields.Item(sLevelUniqueName);
                if (field.Orientation == Excel.XlPivotFieldOrientation.xlPageField)
                {
                    field.EnableMultiplePageItems = true;
                }
                System.Array arrNewVisibleItems = dictLevelsOfFoundMembers[sLevelUniqueName].ToArray();
                pivotField.VisibleItemsList = arrNewVisibleItems;
                if (field.Orientation == Excel.XlPivotFieldOrientation.xlHidden)
                {
                    field.Orientation = Excel.XlPivotFieldOrientation.xlRowField; //if it's not in the PivotTable, then add it to rows
                }
                pivotField.ClearValueFilters();
                pivotField.ClearLabelFilters();
            }

推荐阅读