☰

無法在 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

這是我正在處理的文件的副本。它顯示原始資料透視表，然後顯示成品。我必須做三張紙，所以我必須循環瀏覽每一張紙。 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，並且那是您需要更改聚合的那個。

正如我上面所說，您可以在建立資料欄位時執行此操作。

相關內容