Vba excel создание отчета
Обновлено: 07.07.2024
Для обеспечения корректного построения отчетов, содержащих макросы, требуется установить доверие на доступ к объектной модели VBA .
В Microsoft Word 2007 это осуществляется следующей последовательностью действий:
В Microsoft Word 2010/2013/2016:
в окне "Параметры Word" выбрать закладку "Центр управления безопасностью", нажать кнопку "Параметры центра управления безопасностью"; выбрать закладку "Параметры макросов", в разделе "Параметры макросов для разработчика" установить опцию "Доверять доступ к объектной модели проектов VBA ".Начало создания макроса
Макрос для отчетов формата Word
Для того, чтобы создать или просмотреть макрос для отчета формата Word необходимо:
на ленте "Разработчик" в группе "Код" нажать кнопку "Visual Basic" (или сразу нажать Alt+F11). Откроется окно приложения "Microsoft Visual Basic for Application"; В окне "Project – TemplateProject" в дереве открыть узел "TemplateProject - Microsoft Word Objects – ThisDocument"; В правой части окна приложения, где приведен код, будет макрос "ПослеВыполненияОтчета".
Если макроса там не будет, его можно создать самостоятельно, введя в данном окне строки начала и конца макроса, а между ними - сам текст макроса:
Sub ПослеВыполненияОтчета(ob As Variant, app As Variant)
Внимание! В Word 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:
Макрос для отчетов формата Excel
Для того, чтобы создать или просмотреть макрос для отчета формата Excel, необходимо:
В окне "Project – VBAProject" в дереве открыть узел "VBAProject - Modules – Module1"; В правой части окна приложения, где приведен код, необходимо перейти к макросу "ПослеВыполненияОтчета".
Внимание! В Excel 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:
Макрос "ПослеВыполненияОтчета"
Любой отчет изначально содержит следующий макрос:
Этот макрос будет запускаться после вставки данных в отчет. Он используется в отчетах баз данных, поставляемых с программой. Код по обработке отчета следует вставлять в этот макрос.
Переменные, которые передаются в макрос:
"app" − приложение, от которого был вызван отчет, то есть Business Studio.Внимание! Просмотреть объектную модель Word для разработки макросов в редакторе Visual Basic можно по клавише F2.
Название привязки для макроса
При решении ряда задач средствами программирования бывает удобно оперировать данными привязок отчета. Для этого необходимо:
Ниже описано, как это это делается для привязок разных типов.
Тип привязки: Объект
В отчете такие привязки представлены полями.
Использование названия привязки в коде:
Тип привязки: Список и Фильтр
В отчете такие привязки представлены таблицей.
Использование названия привязки в коде:
Работа с таблицами
В отчетах Business Studio присутствует большое количество таблиц. Поэтому высока вероятность того, что основные задачи будут связаны с их обработкой.
Примеры ключевых функций, которые будут полезны при работы с таблицами, приведены ниже.
Работа с таблицей через название привязки
Работа с таблицей через её номер в документе
При работе с таблицами следует помнить ряд моментов:
Таблицы могут содержать объединенные ячейки. В случае перебора всех строк таблицы может возникнуть ошибка. Чтобы этого не было, необходимо использовать команду игнорирования ошибки: При получении текста таблицы следует помнить, что полученное значение ячейки так же содержит в себе 2 служебных символа. Для обработки данных ячейки без этих служебных символов следует произвести «очистку» значения. Например:Приведенная выше информация не является полной для работы с таблицами, а лишь обращает внимание на наиболее возможные ситуации.
Определение направления формирования отчета
Один и тот же отчет Business Studio может рассматриваться из разных мест:
одиночный документ Word или Excel или несколько документов,Также могут быть и разные задачи по обработке отчета кодом в зависимости от направления формирования отчета. Для определения направления формирования отчета используются параметры: BSInThread, BSHtml и BSPortal.
| Направление формирования отчета | BSInThread | BSHtml | BSPortal |
|---|---|---|---|
| Одиночный файл (Word, Excel) | False | False | False |
| Пакетное формирование (Word, Excel) | True | False | False |
| HTML -публикация | True | True | False |
| Business Studio Portal | True | True | True |
Пример использования в коде:
Примечание. При формировании отчета типа HTML (HTML -публикация, Business Studio Portal) так же происходит формирование и отдельного файла типа Word.Работа со ссылками в отчетах для HTML
Для "нормализации" отображения данных следует сначала использовать следующий код:
Работа с копией отчета, содержащей макрос
При копировании отчета названия всех привязок изменяются.
В случае, если в отчете используется макрос, который "опирается" на названия привязок, то следует помнить, что после копирования такой отчет не сможет формироваться таким же образом как оригинал без дополнительной обработки.
Для корректной работы макроса рекомендуется использование проверки на предмет наличия привязки в отчете.
Пример проверки наличия привязки типа "Список". В документе это закладки.
Пример проверки наличия привязки типа Объект
Определяем название привязки типа "Объект", которая представляет нужное поле в отчёте (см. статью Руководство пользователя → Пример определения названия привязки типа "Объект").
Примечание. Закладки, не относящиеся к привязкам, например, созданные пользователем самостоятельно, при копировании отчетов не изменяются.Обращение к данным модели бизнес-архитектуры
Из отчета можно напрямую обращаться к данным модели бизнес-архитектуры. Это делается двумя способами:
Через обращение к параметрам объекта, от которого был вызван отчет.Обращение к параметрам объекта
Обращение к параметрам объекта, от которого был вызван отчет, происходит таким образом:
<СистемноеНазваниеПараметра> − это системное название параметра объекта, которое можно просмотреть в Объектной модели (см. Руководство пользователя → Объектная модель).Например, при выводе отчета от единицы деятельности получить данные названия единицы деятельности можно таким образом:
Использование механизма OLE
Механизм OLE предоставляет более широкие возможности по обращению к данным модели бизнес-архитектуры и выводу этих данных в отчетах по сравнению с обращением просто как данным объекта, от которого был вызван отчет.
Описание механизма OLE подробно описано в главе Руководство технического специалиста → Работа с Business Studio через OLE. Для задействования OLE в отчетах конкретной базы нет необходимости во включении опции "Разрешено использование OLE".
Внимание! В отчетах при работе с OLE недоступны все методы класса "Система.OleApplication", поскольку в макросе "ПослеВыполненияОтчета" есть аргумент "app", который является приложением Business Studio, а не "Система.OleApplication".
Несколько задач в одном макросе
Бывают ситуации, когда:
В рамках одного отчета необходимо решить более чем одну задачу. Например, форматирование полей типа RTF и вставка столбца в таблицу. Одну и ту же задачу необходимо применять к разным отчетам. Для этого необходимо скопировать код VBA , который решает поставленную задачу. И будет удобней, если этот код не пересекается с кодами других задач. Отладку кода удобно вести по одной из задач, на время отключив выполнение других задач.В таких случаях каждую решаемую задачу удобно оформлять отдельным макросом. А в ключевом макросе "ПослеВыполненияОтчета" лишь вызывать по очереди нужные макросы. Все эти макросы оформляются в том же окне, где и ключевой макрос и делается так:
Если такие макросы имеют одинаковые части кода, то их тоже можно (и рекомендуется) выносить в отдельные макросы или функции в том же окне с ключевым макросом. Например, функция для определения присутствия в отчете необходимой привязки типа "Список" (см. Работа с копией отчета, содержащим макрос) может быть следующей:
Использование этой функции из макроса может быть таким:
Отладка кода
Разработка макроса, как и любая другая работа, это определенный путь, в котором будут допущены ошибки и будет их исправление. Ошибки могут быть обнаружены сразу или не видны при первом взгляде. Более детально об этом описано в главе Подходы к отладке кода.
Примеры решения задач с использованием макросов
Примеры работы с отчетами при использовании макросов приведены в статье Примеры решения задач.
Для обеспечения корректного построения отчетов, содержащих макросы, требуется установить доверие на доступ к объектной модели VBA .
В Microsoft Word 2007 это осуществляется следующей последовательностью действий:
В Microsoft Word 2010/2013/2016:
в окне "Параметры Word" выбрать закладку "Центр управления безопасностью", нажать кнопку "Параметры центра управления безопасностью"; выбрать закладку "Параметры макросов", в разделе "Параметры макросов для разработчика" установить опцию "Доверять доступ к объектной модели проектов VBA ".Примечание
Начало создания макроса
Макрос для отчетов формата Word
Для того, чтобы создать или просмотреть макрос для отчета формата Word необходимо:
на ленте "Разработчик" в группе "Код" нажать кнопку "Visual Basic" (или сразу нажать Alt+F11). Откроется окно приложения "Microsoft Visual Basic for Application"; В окне "Project – TemplateProject" в дереве открыть узел "TemplateProject - Microsoft Word Objects – ThisDocument"; В правой части окна приложения, где приведен код, будет макрос "ПослеВыполненияОтчета".
Если макроса там не будет, его можно создать самостоятельно, введя в данном окне строки начала и конца макроса, а между ними - сам текст макроса:
Sub ПослеВыполненияОтчета(ob As Variant, app As Variant)
Внимание!
В Word 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:
Макрос для отчетов формата Excel
Для того, чтобы создать или просмотреть макрос для отчета формата Excel, необходимо:
В окне "Project – VBAProject" в дереве открыть узел "VBAProject - Modules – Module1"; В правой части окна приложения, где приведен код, необходимо перейти к макросу "ПослеВыполненияОтчета".
Внимание!
В Excel 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:
Макрос "ПослеВыполненияОтчета"
Любой отчет изначально содержит следующий макрос:
Этот макрос будет запускаться после вставки данных в отчет. Он используется в отчетах баз данных, поставляемых с программой. Код по обработке отчета следует вставлять в этот макрос.
Переменные, которые передаются в макрос:
"app" − приложение, от которого был вызван отчет, то есть Business Studio.Внимание!
Просмотреть объектную модель Word для разработки макросов в редакторе Visual Basic можно по клавише F2.
Название привязки для макроса
При решении ряда задач средствами программирования бывает удобно оперировать данными привязок отчета. Для этого необходимо:
Ниже описано, как это это делается для привязок разных типов.
Тип привязки: Объект
В отчете такие привязки представлены полями.
Использование названия привязки в коде:
Тип привязки: Список и Фильтр
В отчете такие привязки представлены таблицей.
Использование названия привязки в коде:
Работа с таблицами
В отчетах Business Studio присутствует большое количество таблиц. Поэтому высока вероятность того, что основные задачи будут связаны с их обработкой.
Примеры ключевых функций, которые будут полезны при работы с таблицами, приведены ниже.
Работа с таблицей через название привязки
Работа с таблицей через её номер в документе
При работе с таблицами следует помнить ряд моментов:
Таблицы могут содержать объединенные ячейки. В случае перебора всех строк таблицы может возникнуть ошибка. Чтобы этого не было, необходимо использовать команду игнорирования ошибки: При получении текста таблицы следует помнить, что полученное значение ячейки так же содержит в себе 2 служебных символа. Для обработки данных ячейки без этих служебных символов следует произвести «очистку» значения. Например:Приведенная выше информация не является полной для работы с таблицами, а лишь обращает внимание на наиболее возможные ситуации.
Определение направления формирования отчета
Один и тот же отчет Business Studio может рассматриваться из разных мест:
одиночный документ Word или Excel или несколько документов,Также могут быть и разные задачи по обработке отчета кодом в зависимости от направления формирования отчета. Для определения направления формирования отчета используются параметры: BSInThread, BSHtml и BSPortal.
| Направление формирования отчета | BSInThread | BSHtml | BSPortal |
|---|---|---|---|
| Одиночный файл (Word, Excel) | False | False | False |
| Пакетное формирование (Word, Excel) | True | False | False |
| HTML -публикация | True | True | False |
| Business Studio Portal | True | True | True |
Пример использования в коде:
Примечание
Работа со ссылками в отчетах для HTML
Для "нормализации" отображения данных следует сначала использовать следующий код:
Работа с копией отчета, содержащим макрос
При копировании отчета названия всех привязок изменяются.
В случае, если в отчете используется макрос, который "опирается" на названия привязок, то следует помнить, что после копирования такой отчет не сможет формироваться таким же образом как оригинал без дополнительной обработки.
Для корректной работы макроса рекомендуется использование проверки на предмет наличия привязки в отчете.
Пример проверки наличия привязки типа "Список". В документе это закладки.
Пример проверки наличия привязки типа Объект
Определяем название привязки типа "Объект", которая представляет нужное поле в отчёте (см. статью Руководство пользователя → Пример определения названия привязки типа "Объект").
Примечание
Закладки, не относящиеся к привязкам, например, созданные пользователем самостоятельно, при копировании отчетов не изменяются.
Обращение к данным из базы
Из отчета можно напрямую обращаться к данным из базы бизнес-модели. Это делается двумя способами:
Через обращение к параметрам объекта, от которого был вызван отчет.Обращение к параметрам объекта
Обращение к параметрам объекта, от которого был вызван отчет, происходит таким образом:
<СистемноеНазваниеПараметра> − это системное название параметра объекта, которое можно просмотреть в Объектной модели (см. Руководство пользователя → Объектная модель).Например, при выводе отчета от процесса получить данные названия процесса можно таким образом:
Использование механизма OLE
Механизм OLE предоставляет более широкие возможности по обращению к данным бизнес-модели и выводу этих данных в отчетах по сравнению с обращением просто как данным объекта, от которого был вызван отчет.
Описание механизма OLE подробно описано в главе Руководство технического специалиста → Работа с Business Studio через OLE. Для задействования OLE в отчетах конкретной базы нет необходимости во включении опции "Разрешено использование OLE".
Внимание!
В отчетах при работе с OLE недоступны все методы класса "Система.OleApplication", поскольку в макросе "ПослеВыполненияОтчета" есть аргумент "app", который является приложением Business Studio, а не "Система.OleApplication".
Несколько задач в одном макросе
Бывают ситуации, когда:
В рамках одного отчета необходимо решить более чем одну задачу. Например, форматирование полей типа RTF и вставка столбца в таблицу. Одну и ту же задачу необходимо применять к разным отчетам. Для этого необходимо скопировать код VBA , который решает поставленную задачу. И будет удобней, если этот код не пересекается с кодами других задач. Отладку кода удобно вести по одной из задач, на время отключив выполнение других задач.В таких случаях каждую решаемую задачу удобно оформлять отдельным макросом. А в ключевом макросе "ПослеВыполненияОтчета" лишь вызывать по очереди нужные макросы. Все эти макросы оформляются в том же окне, где и ключевой макрос и делается так:
Если такие макросы имеют одинаковые части кода, то их тоже можно (и рекомендуется) выносить в отдельные макросы или функции в том же окне с ключевым макросом. Например, функция для определения присутствия в отчете необходимой привязки типа "Список" (см. Работа с копией отчета, содержащим макрос) может быть следующей:
Использование этой функции из макроса может быть таким:
Отладка кода
Разработка макроса, как и любая другая работа, это определенный путь, в котором будут допущены ошибки и будет их исправление. Ошибки могут быть обнаружены сразу или не видны при первом взгляде. Более детально об этом описано в главе Подходы к отладке кода.
Примеры решения задач с использованием макросов
Примеры работы с отчетами при использовании макросов приведены в статье Примеры решения задач.
Получение списка файлов в папке и подпапках средствами VBA
Функция FilenamesCollection предназначена для получения списка файлов из папки, с учётом выбранной глубины поиска в подпапках.
Используется рекурсивный перебор папок, до заданного уровня вложенности.
В процессе перебора папок, пути у найденным файлам помещаются в коллекцию (объект типа Collection) для последующего перебора.
К статье прикреплено 2 примера файла с макросами на основе этой функции:
Выпадающий календарь в ячейке (выбор даты на листе Excel)
Надстройка samradDatePicker (русифицированная) для облегчения ввода даты в ячейки листа Excel.
Добавляет в контекстное меня ячеек пункт выбора даты, а при выделении ячеек, содержащих дату, справа от ячейки отображает значок календаря.
Поместите файл надстройки из вложения в папку автозагрузки Excel (C:\Program Files\Microsoft Office\OFFICExx\XLSTART).
В контекстном меню ячеек появляется новый пункт - "Выбрать дату из календаря".
Рядом с ячейками, в которые уже введена дата, будет отображаться маленький календарик, щелчок по которому вызовет большой календарь - для выбора даты.
Удаление (скрытие) строк по условию
макрос удалит на листе все строки, в которых содержится искомый текст:
Вывод диалоговых окон выбора файла и папки средствами VBA (выбрать файл или папку)
Функции GetFileName и GetFilePath по сути аналогичны, и предназначены для вывода диалогового окна выбора файла
(при этом можно указать стартовую папку для поиска файла, и тип/расширение выбираемого файла)
Функция GetFilenamesCollection позволяет выборать сразу несколько файлов в одной папке.
Функция GetFolderPath работает также, только служит для вывода диалогового окна выбора папки.
Поиск в Excel
Наверняка, вы сталкивались с ситуацией, когда необходимо производить поиск некоторого значения по всей книге Excel (искать частичное совпадение на всех листах активной книги)
Штатными средствами Excel вывести поле для поиска на панель инструментов не удаётся, а вызывать каждый раз диалоговое окно нажатием комбинации клавиш Ctrl + F не всегда удобно.
На помощь придёт эта надстройка - она формирует в строке меню Excel 2003 поле для поиска по всем листам:
Достаточно ввести искомый текст, и нажать клавишу Enter, - и перед вами полный список всех подходящих ячеек со всех листов книги.
Поводом для заметки послужила статья на Хабре, в которой автор описывал, как он решал на Python задачу сбора и анализа метаданных из файлов Excel.
Эта заметка более подробно раскрывает всем известный тезис: Под конкретную задачу надо выбирать наиболее подходящий инструмент применимо к офисной автоматизации.
VBA и Python
VBA (Visual Basic for Applications), де-факто, самый популярный язык для автоматизации Microsoft Office. Доступен из коробки, помимо Excel, работает в PowerPoint, Outlook, Access, Project и других приложениях.
Если задать вопрос: «Какой язык программирования выбрать первым», то где-то в 90% всех случаев будет предложен Python. На практике здесь может быть и любой другой язык, но, исходя из популярности языка и своего опыта, буду сравнивать с ним.
В общем виде можно описать ситуацию через подобный график:
Детального сравнения не будет, рассмотрим основные killer-фичи, в ситуации, когда junior-программист/офисный сотрудник хочет автоматизировать что-либо, связанное с MS Office, и у него есть возможность выбора между языками.
Если в силу разных причин возможности выбора нет, то и сравнивать нечего.
В пользу VBA
- Отличная работа с объектной моделью Excel и других приложений MS Office. Написание кода на VBA для большинства внутренних операций тривиально. У Python, в сравнении с VBA, поддержка объектной модели Office очень слабая.
- Поддержка разных форматов MS Office. Самая большая проблема для внешних языков — это работа с разными форматами файлов MS Office. Например, xls, xlsx, xlsm файлы могут требовать разных библиотек, так как каждая хорошо работает только со своим форматом файла. Для VBA — это все "файл Excel", работа с которыми в целом одинаково хороша.
- Работа с MS Exchange. Если необходимо обеспечить работу с корпоративной почтой/календарем на Exchange, то далеко не каждом языке есть нормальная библиотека для работы протоколом Exchange. В VBA это решается относительно просто через использование в макросе объектной модели MS Outlook.
- Легкая установка и дистрибуция. К уже установленному офису не надо ничего устанавливать. Чтобы коллега мог воспользоваться программой, достаточно передать ему файл с макросом. Легко сделать надстройку, которая позволит "установить" модель макроса в фон офиса.
- Интерактивность внутри приложений MS Office. Внутри офисных программ можно как просто поставить кнопки запуска макросов, так и (чуть сложнее) сделать целый отдельный UI. Сюда же относится написание своих формул в Excel и то, что макросы могут воздействовать на объекты внутри документов Office в реальном времени.
- Запись макросов. Удобный инструмент, который позволяет записать действия человека в готовый код, для последующего редактирования использования.
В пользу Python (и других внешних языков программирования)
- Приятный синтаксис и синтаксический сахар. Если коротко, то VBA не отличается выразительностью и удобством. Это вопрос личного вкуса, но для меня Python намного удобнее.
- Богатая экосистема библиотек. Огромный выбор готовых библиотек для работы с внешним миром. Пытаться сделать на VBA программу, взаимодействующую с каким-нибудь внешним API, та еще боль. Занимательно, что как раз для работы с файлами Office библиотеки того же Python — откровенно "на троечку".
- Хорошие средства разработки. Можно выбрать из огромного выбор программ, которые облегчают процесс разработки. Стандартный редактор VBA из Office предлагает очень бедный функционал и, в сравнении с альтернативами из мира Python, откровенно неудобен. Писать код VBA в внешнем редакторе, а потом копировать внутрь офиса для отладки — тоже неудобно.
- Скорость работы. Не проверял скорость однопоточной работы, но, предположу, что в случае однопоточной работы преимущество будет за Python. В любом случае, достаточно тривиально организуется многопоточная обработка данных/файлов, что позволяет говорить в большей достижимой скорости.
Кейсы
Далее приведены конкретные задачи, которые я сам решал или автоматизировал, и когда мне надо было выбрать стек: VBA или Python. Для каждой задачи указан выбранный стек и даны пояснения почему:
- Задача: Программа для проверки всех файлов Excel в директории на предмет наличия скрытых листов
- Мой выбор: VBA. Причины: простота работы с разными форматами файлов Excel и отсутствие внешних взаимодействий.
- Сервис был реализован как почтовый бот, на адрес которого пользователь может переслать файлы Office, а в ответ пользователю по почте приходит ответ с файлами PDF.
- Мой выбор: Логика VBA + Python для мониторинга
- Во-первых, внутренние функции гарантированно сохраняли PDF, аутентичный файлу PowerPoint (внешние библиотеки плохо справляются с рендером PowerPoint).
- Во-вторых, реализация почтового бота, как макроса в MS Outlook решала проблемы работы с корпоративной Exchange почтой. Так, в Python нет нормальной библиотеки для работы с MS Exchange.
- Python использовался для организации мониторинга работы сервиса и нотификации о возможных проблемах
- Мой выбор: VBA. Задача решалась через конвертацию двух файлов в PDF и их объединением с Riffle Shuffle. Так как важно качество конвертации в PDF, то использовались встроенные функции офиса для экспорта в PDF.
- Мой выбор: Python.
- Чтобы вытаскивать из html карточек данные пригодилась библиотека для парсинга html BeautifulSoup.
- Excel-файл создает программа, поэтому мы сами можем решать, какую аналитику рассчитывать уже в нем, а какую еще на стадии подготовки данных в Python.
- Мой выбор: VBA. Важно было аккуратно работать с текстом во внутренних объектах файла PowerPoint. Для перевода использовался API от Яндекса, так как он бесплатен для небольших объемов и прост в подклюении. Например, API переводчика Bing я так и не смог заставить работать в VBA, так как там для работы нужен OAuth со своими заморочками. Если бы пришлось работать с Bing, то, наверное, я бы делал сервис-посредник на Python.
- Мой выбор: Python. Хотя API — простой (не требует какой-либо подписи запросов или авторизации) и выдает данные в CSV, выбран Python, так как нет причин выбирать VBA, а на Python писать удобнее.
- Здесь я выбирал очень долго, так как есть много альтернатив:
- Сторонняя готовая система поручений
- База данных с каким-то обработчиком
- Access
- Excel
- Во-первых, Excel сам по себе является готовым UI для работы
- Во-вторых, VBA решает задачу работы с корпоративным Exchange через подключение MS Outlook
- В-третьих, это решение делалось с прицелом на коллег, которым Excel был более понятен, чем что-либо совсем новое
Заключение
Надеюсь, для кого-то заметка будет полезна и позволит сэкономить время на выборе стека для решения своих задач.
Читайте также: