Невозможно добавить CubeField/PivotField как поле данных в VBA
Я работаю над очень длинным макросом для своей работы, и я почти закончила, но это последняя часть, и, независимо от того, что я делаю, я не могу найти решение. В этой сводной таблице мне нужно иметь "Менеджеры" в качестве столбцов и все остальное (т.е. все месяцы) в разделе "Значения" с "Значениями" в качестве строк. После перебора кажется, что все поля зарегистрированы как 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. Невозможно установить свойство Function класса 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
Когда я использую устройство записи макросов, чтобы добавить что-то в поле данных, я получаю это:
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
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? Вы должны быть в состоянии отказаться от этого для i = 1 бита 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], это НЕ поле данных. DataField будет что-то вроде [Меры]. [Среднее значение effRent_perBed]
И именно поэтому ваш код потерпел неудачу: вы все еще ссылались на интересующую область, как если бы она все еще была CubeField. Но как только вы попытаетесь добавить его в область DataFields, будет создан новый DataField, и именно для этого вам нужно изменить агрегацию.
Как я уже говорил выше, вы можете сделать это во время создания DataField.