Estoy trabajando en una macro muy larga para mi trabajo y casi estoy terminada, pero esta es la última parte y no importa lo que haga, parece que no puedo encontrar la solución. En esta tabla dinámica, necesito tener "Administradores" como columnas y todo lo demás (es decir, todos los meses) en la sección Valores, con "Valores" como filas. Después de experimentar, parece que todos los campos se registraron como CubeFields en lugar de PivotFields. Cuando ejecuto esto, tan pronto como llega a .Orientation = xlDataField, arroja un "Error de tiempo de ejecución 5...Llamada a procedimiento o argumento no válido". También necesito asegurarme de que esos campos de datos estén promediados y en un formato numérico específico. ¡Nada de lo que hago funciona, y cualquier orientación/solución/solución alternativa sería muy apreciada! A continuación se muestran la macro y un enlace de descarga al archivo para realizar modificaciones.
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 es una edición que intenté hacer basada en la grabadora de macros. Coloca el campo en el campo de valores, pero no hay forma de cambiarlo a un promedio. Simplemente enumera todos los valores como "1(Compruebe la imagen para ver una imagen). Cuando llego a .Function = xlAverage, dice Error 1004: No se puede establecer la propiedad Función de la clase 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
Cuando uso la grabadora de macros para agregar algo al campo de datos, aparece esto:
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 es una copia del archivo en el que estoy trabajando. Muestra la tabla dinámica sin procesar y luego el producto terminado. Tengo que hacer esto para 3 hojas, así que tengo que recorrer cada una de ellas. https://drive.google.com/uc?export=download&id=1NLGg8DVEMHnB2Ad7NAKWySevq8naqFjr
Esto también se publica en otros foros (necesito que la mayor cantidad de ojos puedan verlo lo antes posible) https://www.excelforum.com/excel-programming-vba-macros/1222588-unable-to-add-cubefield-pivotfield-as-a-data-field-in-vba.html#post4856940
Respuesta1
Su pregunta aún podría desarrollarse más. Por ejemplo, no dice si está creando una tabla dinámica desde cero y luego agregando campos, o si esto se ejecuta en una tabla dinámica existente que podría ya tener campos.
Alguien me acaba de señalar elResolución de problemas del pato de goma/publicación de blog, y también te lo señalaré, porque ayuda a las personas a responder si saben todo lo relevante.
Enhttps://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivottable-adddatafield-method-exceldice que la sintaxis para .adddatafield es AddDataField (campo, título, función) y la función es opcional.
Entonces, ¿qué pasa si intentas esto?
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
Hay algunos problemas con su bloque de código original. No entiendo por qué están iterando a través de la colección CubeFields y también iterando a través de un recuento de campos cúbicos. es decir, este bit:
For Each cubField In pvtTable.CubeFields
For i = 1 To pvtTable.CubeFields.Count
¿No es eso simplemente recorrer todo en iteración por el cuadrado de Cubefields.count? Debería poder deshacerse de ese bit For i = 1 To pvtTable.CubeFields.Count y simplemente usar la referencia cubField directamente.
Creo que la razón por la que su código no pudo establecer el resumen en XLAverage es porque una vez que agrega un campo al área de Datos, el nombre cambia efectivamente. Puede ver esto si deja el campo de datos en su lugar y ejecuta 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
Eso imprimirá los nombres de todos los campos en la ventana inmediata (suponiendo que la tenga abierta), momento en el cual verá que si bien tendrá un resultado para [effRent_perBed].[Manager], este NO es el campo de datos. El campo de datos será algo así como [Medidas].[Promedio de effRent_perBed]
Y es por eso que su código falló: todavía se refería al campo de interés como si todavía fuera un CubeField. Pero tan pronto como lo intentes, agrégalo al área DataFields, se crea un nuevo DataField yeso esaquel para el que necesitabas cambiar la agregación.
Sin embargo, como dije anteriormente, puedes hacer esto en el momento de crear el campo de datos.