У меня есть код VBA, который запускает SQL-запрос, выгружает его в электронную таблицу Excel 2010 и обновляет 2 сводные таблицы на основе данных. У меня также есть 2 сводные диаграммы на основе сводных таблиц, и каждый раз, когда таблицы обновляются, форматирование диаграмм теряется.
Я следовал инструкциям вРаздел 12.3 этой книгикоторый предложил создать пользовательский тип диаграммы на основе моих предпочтений форматирования и применить его к диаграммам, которые я хочу, чтобы они оставались отформатированными таким образом. Эти инструкции работают отличноеслиЯ вручную изменяю данные, затем вручную обновляю сводную таблицу — диаграммы остаются правильно отформатированными. Однако, когда я выполняю этот код:
For Each pt In NewSheet.PivotTables
pt.RefreshTable
Next
форматирование сразу теряется.
Вот как выглядели данные до этого:
и мои тщательно созданные круговые диаграммы:
Как только я обновлю данные и обновлю таблицы:
Вот что я получаю:
Опять же, применение пользовательского стиля диаграммы сохранит форматирование, если оно было выполнено вручную, но выполнение кода VBA каким-то образом сбрасывает пользовательский тип диаграммы.
решение1
Чтобы добиться этого без использования сводных диаграмм, вам понадобится лишь динамический именованный диапазон и простая (пусть и грубо отформатированная) диаграмма.
1) Создайте определенное Имя, используя эту формулу: Series_A=OFFSET(Sheet!B1,0,0,COUNT(A:A),1), где - Лист1!B1 ссылается на первую ячейку счетчика в вашей сводной таблице (или на любую другую ссылку, если вам удобно работать со СМЕЩЕНИЕМ) - Количество(A:A) - ваш столбец с заголовками строк
2) Создайте диаграмму(ы) с серией=Series_A (и любыми другими, которые вам нужны).
При обновлении сводных таблиц заданные имена будут ссылаться на расширяющиеся/сужающиеся значения в столбце меток строк и возвращать только значения в столбце количества. Не требуются 0, #N/A или вспомогательные столбцы.