Estou trabalhando em uma macro muito longa para o meu trabalho e estou quase terminando, 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, preciso ter "Gerentes" como colunas e tudo o mais (ou seja, todos os meses) na seção Valores, com "Valores" como linhas. Depois de mexer, parece que todos os campos foram registrados como CubeFields em vez de PivotFields. Quando executo isso, assim que chega a .Orientation = xlDataField, ele gera um "Erro de tempo de execução 5... Chamada ou argumento de procedimento inválido". Também preciso ter certeza de que a média desses DataFields está em um formato numérico específico. Nada do que eu faço funciona, e qualquer orientação/correção/solução alternativa seria muito apreciada! Abaixo estão a macro e um link para download do arquivo para ajustes.
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 tentei fazer com base no gravador de macro. 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 chego em .Function = xlAverage, aparece 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 Macro para adicionar algo ao campo de dados, recebo o seguinte:
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 bruta e, em seguida, o produto final. Eu tenho que fazer isso para 3 folhas, então tenho que percorrer cada uma delas. https://drive.google.com/uc?export=download&id=1NLGg8DVEMHnB2Ad7NAKWySevq8naqFjr
Isso também é postado em outros fóruns (preciso ver isso o máximo possível o mais rápido possível) https://www.excelforum.com/excel-programming-vba-macros/1222588-unable-to-add-cubefield-pivotfield-as-a-data-field-in-vba.html#post4856940
Responder1
Sua pergunta ainda precisa ser mais desenvolvida. Por exemplo, você não diz se está criando uma tabela dinâmica do zero e depois adicionando campos, ou se isso é executado em uma tabela dinâmica existente que já pode conter campos.
Alguém acabou de me indicar oSolução de problemas do pato de borracha/ postagem no blog, e vou indicar isso também, porque ajuda as pessoas a responder se sabem tudo o que é relevante.
Nohttps://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivottable-adddatafield-method-exceldiz que 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 seu bloco de código original. Não entendo por que vocês estão iterando pela coleção CubeFields e também por uma contagem de cubefields. ou seja, esta parte:
For Each cubField In pvtTable.CubeFields
For i = 1 To pvtTable.CubeFields.Count
Isso não é apenas iterar tudo pelo quadrado de Cubefields.count? Você deve ser capaz de abandonar esse For i = 1 To pvtTable.CubeFields.Count bit e simplesmente usar a referência cubField diretamente.
Acho que o motivo pelo qual seu código não conseguiu definir o resumo como XLAverage é porque, depois que você adiciona um campo à área Dados, o nome efetivamente muda. 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ê a tenha aberta), momento em que você verá que, embora tenha 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 eisso éaquele para o qual você precisava alterar a agregação.
Como eu disse acima, você pode fazer isso no momento de criar o DataField.