Não é possível adicionar CubeField / PivotField como um campo de dados no VBA

Question

Estou trabalhando em uma Macro muito longa para o meu trabalho e estou quase acabada, mas esta é a última parte e não importa o que eu faça, não consigo descobrir a solução. Nesta tabela dinâmica, eu preciso ter "Gerentes" como as colunas e tudo mais (ou seja, todos os meses) na seção Valores, com "Valores" como as linhas. Depois de mexer, parece que todos os campos foram registrados como CubeFields em vez de PivotFields. Quando eu executo isso, assim que chega a .Orientation = xlDataField, ele lança um "erro de tempo de execução 5 ... chamada ou argumento de procedimento inválido." Eu também preciso ter certeza de que esses DataFields estão em média e em um formato numérico específico. Nada que eu faça funciona, e qualquer orientação / correção / solução alternativa seria muito apreciada! Abaixo estão a macro e um link de download para o arquivo para mexer.

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

Esta é uma edição que eu tentei fazer com base no gravador de macro. Ele coloca o campo no campo de valores, mas não há como alterá-lo para uma média. Ele simplesmente lista todos os valores como "1 (Verifique a imagem para um visual) . Quando eu chegar ao .Function = xlAverage, diz Erro 1004: não é possível definir a propriedade Function da classe PivotField.

                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

Quando uso o gravador de macros para adicionar algo ao campo de dados, recebo isso:

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

Esta é uma cópia do arquivo em que estou trabalhando. Ele mostra a tabela dinâmica e, em seguida, o produto acabado. Eu tenho que fazer isso por 3 folhas, então eu tenho que percorrer cada uma delas. link

Isso também é postado em outros fóruns (eu preciso ter tantos olhos sobre isso o mais rápido possível) link

link

link

    

Answer 1

Sua pergunta ainda pode continuar. Por exemplo, você não diz se está criando uma Tabela Dinâmica desde o início e, em seguida, adicionando campos, ou se isso é executado em uma Tabela Dinâmica existente que já possa ter campos nela.

Alguém acabou de me indicar a postagem / postagem do blog Rubber Duck Problem , e estou também vou apontar para você, porque ajuda as pessoas a responderem se souberem tudo o que é relevante.

No link , diz a sintaxe para .adddatafield é AddDataField (Field, Caption, Function) com a função sendo opcional.

Então, o que acontece se você tentar isso?

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

Existem alguns problemas com o seu bloco de código original. Não entendo por que vocês estão interagindo com a coleção CubeFields e também iterando por meio de uma contagem de cubefields. ou seja, este bit:

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

Isso não vai apenas percorrer tudo pelo quadrado do Cubefields.count? Você deve ser capaz de abandonar esse bit i = 1 para pvtTable.CubeFields.Count e simplesmente usar a referência cubField diretamente.

Acho que o motivo pelo qual seu código não conseguiu definir o resumo para XLAverage é porque, depois de adicionar um campo à área Data, o nome é alterado de forma efetiva. Você pode ver isso se deixar o DataField no lugar e executar este código:

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

Isso imprimirá os nomes de todos os campos na janela imediata (supondo que você tenha aberto), no ponto em que você verá que enquanto você terá um resultado para [effRent_perBed]. [Manager], este NÃO é o campo de dados . O DataField será algo como [Measures]. [Average of effRent_perBed]

E é por isso que seu código falhou: você ainda estava se referindo ao campo de interesse como se ainda fosse um CubeField. Mas assim que você tentar adicioná-lo à área DataFields, um novo DataField será criado e esse o que você precisou alterar a agregação.

Como eu disse acima, você pode fazer isso no momento em que criar o DataField.