[Разработка для Office 365, Visual Basic for Applications] Как упростить рутинные задачи, используя VBA
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
Привет, Хабр! Я Сергей Чебарев, аналитик в команде визуализации данных «Северсталь-инфокома», и сегодня я расскажу, как можно, используя 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 помогает собирать и обрабатывать данные из разных источников.Если вам интересны похожие способы избавления от рутины, пишите комментарии, я расскажу о других кейсах в следующих статьях.
===========
Источник:
habr.com
===========
Похожие новости:
- [Анализ и проектирование систем, 1С-Битрикс, Бизнес-модели, 1С] Обмен сделками Битрикс24 и 1С
- [Ненормальное программирование, Usability, ECM/СЭД, Управление проектами, Подготовка технической документации] АнтиBIMing
- [Разработка для Office 365, Дизайн, История IT, Софт, IT-компании] Microsoft в 2022 году поменяет шрифт по умолчанию в Office
- [Системное администрирование, PowerShell, Лайфхаки для гиков] Автоматизируем ведение большого количества пользователей в AD
- [Open source, Разработка для Office 365, Лайфхаки для гиков] Получится ли сэкономить, отказавшись от Microsoft Office?
- [Информационная безопасность, .NET, PowerShell, Visual Basic for Applications] Созданные с помощью библиотеки .NET документы Excel обходят проверки безопасности (перевод)
- [] How to Restore an Unsaved or Overwritten Microsoft Excel File?
- [] How to Password Protect an MS Word, Excel or PowerPoint file: manual for dummies?
- [Data Mining, Microsoft SQL Server, Визуализация данных, Хранилища данных, Data Engineering] Как Microsoft Analysis Services финансовым аналитикам жизнь упростил
- [Ненормальное программирование, Программирование, Софт] Я пользуюсь Excel, чтобы писать код (перевод)
Теги для поиска: #_razrabotka_dlja_office_365 (Разработка для Office 365), #_visual_basic_for_applications, #_severstalitpeople, #_vba, #_excel, #_outlook, #_blog_kompanii_severstal (
Блог компании Северсталь
), #_razrabotka_dlja_office_365 (
Разработка для Office 365
), #_visual_basic_for_applications
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 26-Ноя 10:19
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
|
Привет, Хабр! Я Сергей Чебарев, аналитик в команде визуализации данных «Северсталь-инфокома», и сегодня я расскажу, как можно, используя VBA и приложения Office, автоматизировать рутину в работе. VBA (Visual Basic for Applications) – это упрощенная версия Visual Basic, встроенная в множество продуктов Microsoft Office, и соответственно, эти продукты можно подружить между собой. Самое очевидное применение VBA (который ещё часто и не вполне корректно называют макросами) – это автоматизация типовых задач с множеством повторяющихся шагов, делать которые руками долго, скучно и чревато ошибкам от усталости. Рассмотрим одну из таких задач.Создание событий в Outlook через ExcelИтак, у меня есть задача: необходимо создавать события в календаре и отправлять пользователям приглашения с данными для входа на терминальный сервер. Звучит на первый взгляд не очень сложно, но:
Распишем действия по шагам. 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 помогает собирать и обрабатывать данные из разных источников.Если вам интересны похожие способы избавления от рутины, пишите комментарии, я расскажу о других кейсах в следующих статьях. =========== Источник: habr.com =========== Похожие новости:
Блог компании Северсталь ), #_razrabotka_dlja_office_365 ( Разработка для Office 365 ), #_visual_basic_for_applications |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 26-Ноя 10:19
Часовой пояс: UTC + 5