нельзя установить свойство orientation класса pivotfield
Excel vba: error hiding calculated field in Pivot table
I have written several Subs to show/hide fields in a PivotTable. Now I am trying to do the same with a calculated field, but I get an error when hiding it. I took my code from the recorder and the recorder’s code also halts on the last line. I googled the error message, without serious result.
EDIT on 17 June 2010: I also tried using pt.DataFields instead of pt.PivotFields, with exactly the same behaviour. The error message says «Unable to set the orientation of the PivotField class».
14 Answers 14
after much hair pulling i have found a workaround. if you add more than one pivot field (calculated or otherwise) excel creates a grouped field called Values. you can set the orientation property of PivotField(«Values») to xlHidden and it bullets both fields. So if you want to remove a calculated field, just add a non-calculated field, set PivotField(«Values»).orientation to xlHidden and you’re done.
nobody said it would be pretty.
I wanted to easily remove data fields (calculated fields or not), like it would be done manually.
And I finally found this solution (Excel 2010) :
Well, I will give you the confirmation you need. It seems using the Orientation property on a «Calulated Field» just does not work, and I would have to agree this is a bug and not a common «usage» error. I was able to duplicate «hiding/showing» the field without having to remove («Delete») the calculated field. This allows the user to physically drag the calculated field from the field list after you have progammatically «hidden» the field. This is not a bad solution because it duplicates the user-interface. (Using Excel 2003.)
[original answer] Most likely you cannot hide this item because it is the last visible item. Instead, try removing it.
Here is a little workaround I discovered today, again not very elegant but at least it doesn’t need much code, it will hide ALL the fields and you will need to reshow the ones you want after:
You may run into an error if excel for some reason thinks the datapivotfield is empty, but to fix this just add in another field as a datafield right before the above statement. Also make sure its the letter l not the number 1 in xlHidden vba’s default font has them looking very very similar.
It seems that to hide a calculated field you need to first hide a pivot field called «Values».
I’m assuming that field only seem to exist if you’ve got two or more fields in your xlDataField position.
P.s. Your code still does not work with only one calculated data field
Laurent Bosc’s code checks out so I voted it up. My full code includes adding data after hiding it all. The code is placed on Sheet1(Sheet1).
I don’t think this is an excel bug, I think it’s a ‘feature’. 😉
This is the code I routinely use to do what you are trying to do. These macros were developed on Excel 2002 & 2003. I don’t hide CalculatedFields, I delete them.
I am having the exact same problem as you. It looks like I’m going to have to delete the calculated field and readd it rather than hiding/showing it.
I accidentally discovered a workaround to this the first time I attempted to hide a calculated field, so thought I would share it here:
Instead of modifying the orientation property, you can instead instruct the code to select the cell in the pivot table that contains the title of the calculated field you want to hide, and then delete the selection. This works as long as you have another datafield already in the table. Example below:
Scenario: Pivot table covers the range A1:G10. Calculated field «Margin» is already in the table, and you want to add the data field «Sales» and remove the «Margin» calc field.
Not sure why this works, but I’m glad we at least have this to work with!
.datapivotfield.orientation = xlhidden does not work
I have a vba code which checks all the pivots in specified worksheets to add a range of pivotfield to the values/data section of the pivot because the pivot headers change every 4 weeks.
I have 6 pivots where this code works perfectly, but 2 pivots where I keep getting an error. I have tried to adjust the code but was not succesfull. I used this code to find the type for this pivot field as I thought it should be data, but it turns out to be ‘hidden’:
I then checked this code on comparable fields in the pivots where it is working perfectly, expecting it would be data fields, but again it were hidden fields. I therefore do not understand what is different in these 2 pivots that makes my code not work compared to the pivots where the code works perfectly.
This is the code for the pivots where it is not working:
I only need the pivot to have 1 pivot field in the values section.
When I execute this code, I get this error: 
error 1004: propery orientation of class pivotfield cannot be set
And this line is marked when I click ‘solve error’ (or what it is called in english):
I do not understand why because it works perfectly for the other pivots in the worksheet. The only thing that is different is that for those pivots, the code is slightly different:
Excel vba: ошибка скрытия вычисляемого поля в сводной таблице
Я написал несколько подписок для отображения / скрытия полей в сводной таблице. Теперь я пытаюсь сделать то же самое с вычисляемым полем, но при его скрытии получаю сообщение об ошибке. Я взял свой код с диктофона, и код диктофона также останавливается на последней строке. Я погуглил сообщение об ошибке, без серьезного результата.
ИЗМЕНИТЬ 17 июня 2010 г. : я также пробовал использовать pt.DataFields вместо pt.PivotFields с точно таким же поведением. В сообщении об ошибке говорится: «Не удалось установить ориентацию класса PivotField».
13 ответов
Я не думаю, что это ошибка Excel, я думаю, что это «особенность». 😉
Это код, который я обычно использую, чтобы делать то, что вы пытаетесь сделать. Эти макросы были разработаны в Excel 2002 и 2003. Я не скрываю CalculatedFields, я их удаляю.
У меня такая же проблема, как и у вас. Похоже, мне придется удалить вычисленное поле и прочитать его, а не скрывать / показывать.
Я случайно обнаружил обходной путь, когда впервые попытался скрыть вычисляемое поле, поэтому решил поделиться им здесь:
Вместо изменения свойства ориентации вы можете вместо этого указать коду выбрать ячейку в сводной таблице, которая содержит заголовок вычисляемого поля, которое вы хотите скрыть, а затем удалить выделение. Это работает, если в таблице уже есть другое поле данных. Пример ниже:
Сценарий: сводная таблица охватывает диапазон A1: G10. Расчетное поле «Маржа» уже есть в таблице, и вы хотите добавить поле данных «Продажи» и удалить поле расчета «Маржа».
Код для выполнения:
Не знаю, почему это работает, но я рад, что у нас, по крайней мере, есть с этим работать!
К счастью, есть очень простой способ скрыть поле данных. Вы все ошибались, ошибочно принимая сводные поля с полями данных. Я представляю фрагмент кода, который очищает сводную таблицу независимо от того, сколько сводных полей / полей данных изначально было в сводной таблице:
Вы изменили имя вычисляемого поля? Изначально это была «Сумма моего поля»? Попробуйте взглянуть на свойство SourceName и использовать его по-другому.
Если ваши данные хранятся в модели данных, то вместо PivotFields используйте CubeFields. У меня была та же проблема, и я экспериментировал с простой книгой, в которой не было модели данных, и мой код работал отлично (с использованием PivotFields). Он только вернул ошибку в книге с моделью данных. Итак, я внес это изменение и бум! это сработало! Я предлагаю использовать следующий код:
Благодаря ответу @ user4709164 я получил этот код, он отлично работает для меня:
Все мои сводные столбцы заканчиваются на X или Y, чтобы указать ось, поэтому я использую последний символ для заголовка поля.
После долгого выдергивания волос я нашел обходной путь. если вы добавляете более одного сводного поля (вычисляемого или другого), Excel создает сгруппированное поле с именем «Значения». вы можете установить для свойства ориентации PivotField («Values») значение xlHidden, и оно помечает оба поля. Поэтому, если вы хотите удалить вычисляемое поле, просто добавьте невычисляемое поле, установите PivotField («Values»). Ориентация на xlHidden, и все готово.
Я хотел легко удалить поля данных (вычисляемые поля или нет), как если бы это делалось вручную.
И я наконец нашел это решение (Excel 2010):
Вот небольшой обходной путь, который я обнаружил сегодня, опять же не очень элегантный, но, по крайней мере, для него не нужно много кода, он скроет ВСЕ поля, и вам нужно будет повторно показать те, которые вы хотите, после:
Вы можете столкнуться с ошибкой, если Excel по какой-то причине считает, что поле datapivotfield пусто, но для исправления этого просто добавьте другое поле в качестве поля данных прямо перед указанным выше оператором. Также убедитесь, что это буква l, а не цифра 1 в шрифте xlHidden vba по умолчанию, они выглядят очень похожими.
Похоже, чтобы скрыть вычисляемое поле, вам нужно сначала скрыть сводное поле под названием «Значения».
Я предполагаю, что это поле существует только в том случае, если у вас есть два или более полей в вашей позиции xlDataField.
P.s. Ваш код по-прежнему не работает только с одним вычисляемым полем данных
Код Лорана Боска подтвердился, поэтому я проголосовал за него. Мой полный код включает добавление данных после их скрытия. Код помещается на Лист1 (Sheet1).
Delphi – Невозможно изменить функцию в Excel PivotField
Delphi 10/Seattle, Excel 2013. Я использую Delphi для создания плагина Excel. Этот плагин создаст сводную таблицу. Моя задача – установить конкретную FUNCTION, которую я хочу (Count vs Average vs Sum и т.д.). Если я НЕ устанавливаю функцию, используется значение по умолчанию (COUNT), и мой код работает нормально, сводная таблица как и должна быть. Если я попытаюсь изменить функцию на PivotField, я получаю сообщение об ошибке “Невозможно установить свойство Function класса PivotField”. При поиске ошибки я обнаружил 2 распространенных причины. (1) Таблица сводных данных должна иметь стандартную версию xlPivotTableVersion15 и (2) ориентацию PivotField следует установить на xlDataField. Я сделал и то и другое, и когда мышь, среда IDE показывает значения Integer для обоих, поэтому я знаю, что они определены.
Я попытался получить (в отличие от настройки) значение функции PivotField.function. Я получаю аналогичную ошибку “Невозможно получить свойство Function класса PivotField”. Столбец, который мне нужен Sum, называется “#Clicks”. Оскорбительная строка кода
Если я прокомментирую эту строку, моя программа работает нормально, хотя я получаю функцию COUNT по умолчанию вместо функции SUM, которую я хочу.
Любые идеи оценили. Обратите внимание, что когда строка закомментирована, и я запускаю код, я могу перейти в Excel и перейти в списки PivotField и изменить функцию от Count to Sum. Вот мой полный код.
Обновление. Я могу воспроизвести ваши проблемы с Set_Function и GetFunction с помощью Seattle и Excel 2007, поэтому, пожалуйста, не обращайте внимания на исходную версию моего ответа.
Тем не менее, я нашел способ использовать CreateDataField для создания PivotField с Function xlCount, и это очень просто.
При заданных локальных переменных
следующий код выполняется без жалобы и правильно
Я оставлю вас попробовать другие значения XlConsolidationFunction у меня достаточно этой проблемы!
У меня есть WorkBook с таблицей имен компаний, датами и суммами выплаты дивидендов. Заголовками таблицы являются Компания, PaymentDate и Amount.
Код ниже работает для меня и позволяет мне выбрать функцию, которая будет применяться к столбцу Сумма, просто указав имя функции как параметр Caption AddDataField. Я использовал последнее связывание, в основном, для упрощения настройки, и поэтому я могу легко опустить аргументы для параметров, которые я не хочу указывать.
Нельзя установить свойство orientation класса pivotfield
Когда я использую устройство записи макросов, чтобы добавить что-то в поле данных, я получаю это:
Это копия файла, над которым я работаю. Он показывает необработанную сводную таблицу, а затем готовый продукт. Я должен сделать это для 3 листов, поэтому я должен циклически проходить каждый лист. https://drive.google.com/uc?export=download&id=1NLGg8DVEMHnB2Ad7NAKWySevq8naqFjr
Ваш вопрос все еще может быть связан с дальнейшим развитием. Например, вы не говорите, создаете ли вы сводную таблицу с нуля, а затем добавляете поля, или она запускается на существующей сводной таблице, в которой уже могут быть поля.
Кто-то только что указал мне на сообщение « Решение проблем с резиновой уткой» / блог, и я тоже собираюсь указать вам на это, потому что это помогает людям ответить, если они знают все, что имеет отношение к делу.
Так что произойдет, если вы попробуете это?
Есть некоторые проблемы с вашим оригинальным блоком кода. Я не понимаю, почему вы перебираете коллекцию CubeFields, а также перебираете счетчики кубических полей. то есть этот бит:
Разве это не будет проходить через все по квадрату Cubefields.count? Вы должны иметь возможность отказаться от этого для i = 1 бита pvtTable.CubeFields.Count и просто напрямую использовать ссылку cubField.
Я думаю, что причина, по которой вашему коду не удалось установить суммирование для XLAverage, заключается в том, что после добавления поля в область данных имя эффективно меняется. Вы можете увидеть это, если оставите DataField на месте и запустите этот код:
Это выведет имена всех полей в непосредственное окно (при условии, что оно открыто), и в этот момент вы увидите, что, хотя у вас будет результат для [effRent_perBed]. [Manager], это НЕ поле данных. DataField будет что-то вроде [Меры]. [Среднее значение effRent_perBed]
И именно поэтому ваш код потерпел неудачу: вы все еще ссылались на интересующую область, как если бы она все еще была CubeField. Но как только вы попытаетесь добавить его в область DataFields, будет создан новый DataField, и именно для этого вам нужно изменить агрегацию.
Как я уже говорил выше, вы можете сделать это во время создания DataField.