☰

无法在 VBA 中将 CubeField/PivotField 添加为数据字段

tag-icon • tag-icon microsoft-excel vba macros microsoft-excel-2016 pivot-table
无法在 VBA 中将 CubeField/PivotField 添加为数据字段

我正在为工作编写一个很长的宏，我快要完成了，但这是最后一部分，无论我做什么，我似乎都无法找到解决办法。在这个数据透视表中，我需要将“经理”作为列，将其他所有内容（即所有月份）放在值部分，将“值”作为行。经过一番修改后，似乎所有字段都注册为 CubeFields 而不是 PivotFields。当我运行它时，只要它到达 .Orientation = xlDataField，它就会抛出“运行时错误 5...无效的过程调用或参数”。我还需要确保这些数据字段是平均的并且采用特定的数字格式。我所做的一切都没有用，任何指导/修复/解决方法都将不胜感激！以下是宏和用于修改的文件的下载链接。

Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String

Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
    For i = 1 To pvtTable.CubeFields.Count
        With pvtTable.CubeFields(i)
            If .Name = "[effRent_perBed].[Manager]" Then
                .Orientation = xlColumnField
           Else:
                .Orientation = xlDataField
                'has to be averaged
                'has to have number format of ##0.00
            End If

        End With
    Next
Next

这是我尝试基于宏录制器进行的编辑。它将字段放在值字段中，但无法将其更改为平均值。它只是将所有值列为“1（查看图片以了解详情）。当我到达 .Function = xlAverage 时，它​​显示错误 1004：无法设置 PivotField 类的 Function 属性。

                If Name = "[effRent_perBed].[Manager]" Then
                    .Orientation = xlColumnField
                Else:
                    With ActiveSheet.PivotTables(1)
                        .AddDataField ActiveSheet.PivotTables(1) _
                        CubeFields(cubName), _
                        "Average of " & cubName
                    End With

                    With ActiveSheet.PivotTables(1).PivotFields(cubName)
                        .Caption = "Average of " & cubName
                        .Function = xlAverage
                    End With

当我使用宏记录器向数据字段添加某些内容时，我得到以下信息：

ActiveSheet.PivotTables("effRent_perBed_Pivot").CubeFields.GetMeasure _
    "[effRent_perBed].[Jan-16]", xlSum, "Sum of Jan-16"
ActiveSheet.PivotTables("effRent_perBed_Pivot").AddDataField ActiveSheet. _
    PivotTables("effRent_perBed_Pivot").CubeFields("[Measures].[Sum of Jan-16]"), _
    "Sum of Jan-16"
With ActiveSheet.PivotTables("effRent_perBed_Pivot").PivotFields( _
    "[Measures].[Sum of Jan-16]")
    .Caption = "Average of Jan-16"
    .Function = xlAverage
End With

这是我正在处理的文件的副本。它显示了原始数据透视表，然后是成品。我必须对 3 张工作表执行此操作，因此我必须循环浏览每张工作表。 https://drive.google.com/uc?export=download&id=1NLGg8DVEMHnB2Ad7NAKWySevq8naqFjr

这也发布在其他论坛上（我需要尽快让尽可能多的人关注这个） https://www.excelforum.com/excel-programming-vba-macros/1222588-unable-to-add-cubefield-pivotfield-as-a-data-field-in-vba.html#post4856940

https://www.mrexcel.com/forum/excel-questions/1046038-unable-add-cubefield-pivotfield-data-field-vba.html

https://www.ozgrid.com/forum/node/1200403

答案1

您的问题仍需要进一步充实。例如，您没有说明是否要从头开始创建数据透视表，然后添加字段，或者这是否在可能已经包含字段的现有数据透视表上运行。

有人刚刚指出小黄鸭解决问题/博客文章，我也会向你指出这一点，因为如果人们知道所有相关内容，这将有助于他们回答。

在https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivottable-adddatafield-method-excel它说 .adddatafield 的语法是 AddDataField( Field , Caption , Function )，其中函数是可选的。

那么如果你尝试这样做，会发生什么情况？

If Name = "[effRent_perBed].[Manager]" Then
    .Orientation = xlColumnField
 Else:
    With ActiveSheet.PivotTables(1)
        .AddDataField ActiveSheet.PivotTables(1) _
            CubeFields(cubName), _
            "Average of " & cubName, _
            xlAverage
    End With
End if

您的原始代码块存在一些问题。我不明白为什么您既要遍历 CubeFields 集合，又要遍历立方体字段的数量。例如：

For Each cubField In pvtTable.CubeFields
    For i = 1 To pvtTable.CubeFields.Count

这难道不是要通过 Cubefields.count 的平方来迭代所有内容吗？您应该能够放弃 For i = 1 To pvtTable.CubeFields.Count 位，而只需直接使用 cubField 引用即可。

我认为您的代码无法将汇总设置为 XLAverage 的原因是，一旦您将字段添加到数据区域，名称就会有效更改。如果您将 DataField 保留在原处并运行以下代码，就会看到这一点：

Sub Macro1()
'
' Macro1 Macro
'
Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String

Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
    Debug.Print cubField.Name
Next

End Sub

这会将所有字段的名称打印到即时窗口（假设您已打开它），此时您将看到，虽然您将获得 [effRent_perBed].[Manager] 的结果，但这不是数据字段。数据字段将类似于 [Measures].[Average of effRent_perBed]

这就是你的代码失败的原因：你仍然引用感兴趣的字段，就好像它仍然是一个 CubeField。但是，一旦你尝试将它添加到 DataFields 区域，就会创建一个新的 DataField，并且那是您需要更改聚合的那个。

正如我上面所说的，您可以在创建 DataField 时执行此操作。

相关内容