Автоматическое обновление диаграмм в power point, связанных в Excel (VBA)

В этой статье мы рассмотрим способы связывания диаграмм созданных в приложении Excel с диаграммами в презентации Power point.

Самый простой способ - копировать диаграмму на листе Excel и вставить ее специальным способом

Выбрав команду "Использовать конечную тему и связать данные" диаграмма становится связанной с файлом Excel и изменяется на слайде автоматически при ее изменении на листе Excel

Несомненным преимуществом этого способа является то, что обновление происходит автоматически (достаточно изменить значение параметра на листе Excel, и значение этого параметра автоматически изменяется на слайде). Но помимо преимущества у этого способа есть и довольно неприятный недостаток - довольно легко разрывается связь между диаграммами (достаточно изменить имя файла или папки, где лежат файлы excel и powerpoint) . Кроме того, в случае большого количества диаграмм вам необходимо вручную связать все диаграммы из приложения Excel с Power point, что может занять довольно большое время, а также нужно учесть то, что в случае разрыва связи между файлами, настраивать их снова тоже нужно будет вручную.

Более надежный способ - использовать VBA.

С помощью VBA можно не только связать диаграммы между собой, но и автоматически создавать.

На рисунке в левой части - лист ексель - источник данных, в правой диаграммы построенные в powerpoint по значениям на листе - источнике. Обновление диаграмм в данном случае выполняется по нажатию кнопки "Обновить"

Связь между excel и powerpoint будем организовывать в приложении excel, поэтому перед тем, как писать код, для работы с приложением powerpoint в excel нужно добавить библиотеку Microsoft PowerPoint 16.0 Object Library.

Для этого нужно выполнить:

1. В приложении Excel перейдите в редактор VBA с помощью комбинации клавиш Alt+F11
2. Выберите вкладку Tools и из списка команд References

3. В появившейся форме найдите библиотеку Microsoft PowerPoint 16.0 Object Library и добавьте ее с помощью галочки в правой части строки с наименованием библиотеке

Для того, чтобы код VBA находил нужные диаграммы на слайде нужно на листе ексель задать имена всем диапазонам, в которых хранятся значения диаграмм.

выделите диапазон таблицы с данными и в поле наименования диапазона введите наименование диаграммы - например в нашем случае - Диаграмма1

Теперь на слайде в приложении Powerpoint вставьте диаграммы (в нашем примере в екселе их три, поэтому на слайде нужно добавить три диаграммы и задать им имена, соответствующие именам диапазона)

Для того чтобы задать имя диаграмме, найдите на ленте команду "Выделить" и в списке команд нажмите на команду "Область выделения"

Затем поочередно выделяя диаграммы на слайде задайте им имя, соответствующее диапазону соответствующей таблицы на листе excel

Теперь можно переходить к написанию кода

Наберите код самостоятельно или скопируйте и вставьте его в поле ввода кода

Sub СвязатьДиаграммы()

Dim wb As Workbook

Dim r As Range

Dim nm As Name

Dim ppt As PowerPoint.Application

Dim sl As PowerPoint.Slide

Dim sh As PowerPoint.Shape

Dim cht As PowerPoint.Chart 'Chart

Dim chtData As PowerPoint.ChartData

Dim cTable As Excel.ListObject

Set wb = ThisWorkbook

'здесь задаем переменным значения приложения powerpoint

Set ppt = GetObject(, "powerpoint.application")

' и первого слайда, на котором расположены диаграммы

Set sl = ppt.Presentations(1).Slides(1)

'перебираем все именованные диапазоны на листе ексель - источнике данных

For Each nm In ThisWorkbook.Names

Set sh = sl.Shapes(nm.Name) ' - на слайде выбираем фигуру, имя которой соответствует именованному диапазону диаграммы

'здесь задаем переменным параметры диаграммы расположенной на слайде

Set cht = sh.Chart ' диаграмма встроенная в фигуру sh

Set chtData = cht.ChartData 'диапазон данных диаграммы

cht.ChartTitle.Text = nm.Name

chtData.Activate 'активизация окна с листом ексель диаграммы

With chtData

Set r = chtData.Workbook.Worksheets(1).Cells(1, 1) ' первая ячейка на листе диаграммы

Set r = r.CurrentRegion ' расширяем первую ячейку на весь диапазон данных

r.Clear ' очищаем диапазон

wb.Worksheets(1).Range(nm).CurrentRegion.Copy ' копируем диапазон данных с листа ексель - источника данных

r.PasteSpecial xlPasteAll ' вставляем в диапазон данные с листа ексель - источника данных

Set r = r.CurrentRegion 'расширяем диапазон на листе ексель встройнной диаграммы

End With

cht.SetSourceData Source:="=" & Chr(39) & r.Parent.Name & Chr(39) & "!" & r.Address ' меняем диапазон данных встроенной в слайд диаграммы

chtData.Workbook.Close ' закрываем лист ексель встроенной диаграммы

Next

End Sub

Теперь при открытых файлах excel и powerpoint запуская код VBA диаграммы в презентации будут автоматически перерисовываться и соответствовать таблицам на листе excel.

В этом примере предполагается ручное создание диаграмм в приложении powerpoint, при небольшой доработке кода их можно создавать автоматически.

Если статья вам понравилась, ставьте лайк и подписывайтесь на канал.