Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Привет, Хабр! Я Сергей Чебарев, аналитик в команде визуализации данных «Северсталь-инфокома», и сегодня я расскажу, как можно, используя VBA и приложения Office, автоматизировать рутину в работе. VBA (Visual Basic for Applications) – это упрощенная версия Visual Basic, встроенная в множество продуктов Microsoft Office, и соответственно, эти продукты можно подружить между собой.
Самое очевидное применение VBA (который ещё часто и не вполне корректно называют макросами) – это автоматизация типовых задач с множеством повторяющихся шагов, делать которые руками долго, скучно и чревато ошибкам от усталости. Рассмотрим одну из таких задач.
Создание событий в Outlook через Excel
Итак, у меня есть задача: необходимо создавать события в календаре и отправлять пользователям приглашения с данными для входа на терминальный сервер.
Звучит на первый взгляд не очень сложно, но:
Приглашения нужно отправлять из общего почтового ящика
Тело приглашения должно содержать корпоративный стиль оформления
Есть лимит по количеству пользователей на терминальном сервере в сутки, поэтому необходимо следить за количеством событий
Соответственно, мало просто открыть Outlook с календарем, перед созданием события нужно проверить, не превысит ли лимит на текущий день данное приглашение, задать диапазон времени, перенести тело письма из файла или из предыдущего приглашения. Все это требует выполнения большого числа действий и отнимает заметное количество времени.
Как же упростить эту рутину, используя Excel, Outlook и VBA? Рассказываю
Распишем действия по шагам.
1. Создаем книгу Excel и получаем данные из календаря
Используя инструмент «Создать запрос», выбираем источник «Из Microsoft Exchange» и вводим почтовый ящик, где ведутся события. После подключения открывается PowerQuery с данными из почты (сообщения, календарь и прочее). Нам нужны только данные календаря.
В таблице теперь видны все созданные события, но нам необходимо видеть ещё и дни, где пока нет событий. Для этого мы создаем новый запрос и вызываем функцию List.Dates:
Она нам создаст список дней, начиная с сегодняшнего, на 21 день вперед:
Теперь объединим 2 данных запроса в один. Для этого переводим лист с датами в таблицу с помощью инструмента “В таблицу” на вкладке “преобразование” и переходим на запрос, где мы собрали все приглашения из почты, после чего на вкладке “Главная” выбираем инструмент “Добавить запросы”. Выбираем созданную ранее таблицу с датами и объединяем:
*Важно, при объединении столбцы в таблицах должны иметь одинаковые названия.
Создаем дополнительные столбцы «Subject», «Location» и «Статус брони», которые будут нести информацию о теме события, локации события и о том, из какого запроса пришли данные: если из календаря, то «Бронь», если из вызванной функции — «Свободно». Делаем сведение по столбцу «Статус брони» для определения количества свободных окон по дням и присваиваем статус для возможности бронирования. Закрываем PowerQuery и получаем следующую таблицу:
Отлично! Создав столбец “Окон свободно”, мы освободились от подсчета событий и видим возможность бронирования.
2. Создание событий в Outlook
Теперь нам необходимо написать скрипт на VBA для создания событий в Outlook. Для этого переходим во вкладку “Разработчик” в Excel и открываем VisualBasic:
В открывшемся окне выбираем рабочий лист, где находится итоговая таблица по событиям:
Запускать код мы будем, используя двойной клик на строки столбца «Доступность бронирования», и при условии, что статус бронирования «Забронировать»:
Для работы с Outlook мы будем использовать функцию GetObject("Outlook.Application"). GetObject — это функция для приложений VBA. Она используется для прикрепления к запущенным объектам. Более подробно с синтаксисом функции можно ознакомиться в документации Microsoft.
Для соблюдения корпоративного стиля код ищет предыдущее приглашение и копирует из него тело письма. Далее создается новое приглашение, и в него заносится вся необходимая информация.
subjectStr = "Шаблон приглашения" - ‘Поиск заданного приглашения(шаблона)
For Each oAppointmentItem In objNavFolder.Folder.Items
Set P = oAppointmentItem
If InStr(oAppointmentItem.Subject, subjectStr) > 0 Then
Set N = oAppointmentItem
N.Copy ‘Делаем копию шаблона
N.Display
N.Location = Cells(b, 2) ‘место события из ячейки таблицы
N.Subject = Cells(b, 1) ‘название события из ячейки таблицы
N.Start = Cells(b, 3) + 0.33333 ‘время начала события
N.End = Cells(b, 3) + 0.999 ‘время окончания события
Exit For
End If
Next oAppointmentItem
Протестируем. Открываем файл, ищем строку с датой, которая нас интересует, и двойным кликом на статус бронирования запускаем скрипт:
Приглашение на выбранный нами день с необходимой темой и телом письма успешно создано, осталось только указать получателя
3. Обновление данных
Чтобы при открытии книги происходило обновление данных, сохраним скрипт в саму книгу:
Кроме того, отключим фоновое обновление в свойствах подключения таблицы:
Подведем итоги
Итак, мы упростили создание однотипных событий в календаре, а также точно уверены, что не превысим лимит событий на день. Это позволило нам избавиться от рутинной работы и уменьшило вероятность человеческой ошибки.
Конечно, в идеале можно было доработать нашу платформу обучения и полностью автоматизировать процесс бронирования. Но скрипты VBA тем и хороши, что могут заметно упростить жизнь без разработки и изменения существующих систем. Они пригодятся там, где скрипт пишется за несколько часов, а на полноценную автоматизацию и доработку существующих решений нужны недели или месяцы.
Интересно, что подобные решения могут быть не всегда очевидны. Например, в этой задаче изначально вообще не использовался Excel. Кажется, что раз необходимо создавать события, то нужно использовать только Outlook. Но Excel помогает собирать и обрабатывать данные из разных источников.
Если вам интересны похожие способы избавления от рутины, пишите комментарии, я расскажу о других кейсах в следующих статьях.
