Автоматизация составления исполнительной документации по строительству в Excel. Финансы в Excel Обучение excel автоматизация акт

Если вы частый пользователь MS Excel, Вам наверняка приходится ежедневно выполнять однотипные операции. В этом случае макросы Excel помогут записать последовательность действий в виде набора VBA команд. Такой способ отлично подойдет для автоматизации простых задач. Если речь идёт о более сложных задачах, пользователи c навыками программирования могут автоматизировать операции с помощью VBA проектов.

Надстройка «Автоматизация» предлагает принципиально новый подход к автоматизации рутинных задач в Excel:

Создание команд в простой таблице Excel вместо объёмных VBA проектов
Автоматизация даже сложных и многоэтапных операций
Автоматизация возможностей XLTools: SQL запросы, Экспорт в CSV, Редизайн таблицы, т.д.
Создание пользовательских кнопок на панели инструментов
Для продвинутых пользователей и разработчиков

Не обязательно быть знатоком VBA. Если какие-то ваши бизнес-процессы в Excel отнимают слишком много времени, наша команда XLTools поможет их автоматизировать.

Добавить «Автоматизацию» в Excel 2019, 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2019 - 2007, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

Как автоматизировать операции в Excel без VBA [Скачать пособие]

Зачастую VBA макросы Excel разрастаются до сотен строк кода, очень неудобных в работе. Надстройка XLTools «Автоматизация» позволяет писать команды в простых и компактных таблицах Excel. Табличное представление более информативно, наглядно и его легче редактировать. Вы также можете добавить собственные кнопки на панель инструментов Excel для выполнения собственных команд автоматизации.

Надстройка «Автоматизация» — это универсальный инструмент для автоматизации практически любых команд и их последовательностей:

Автоматизация SQL запросов к таблицам Excel: SELECT, GROUP BY, JOIN ON, т.д.
Автоматическое преобразование сводных таблиц в плоский список
автоматический экспорт таблиц Excel в файл CSV
Автоматическое извлечение данных из других книг Excel или CSV файлов
Автоматическая фильтрация таблиц, т.д.

Просто напишите команду, используя пособие > Нажмите «Выполнить команды» > Готово! Генерация результата займёт всего пару секунд.

СКАЧАТЬ ПОСОБИЕ : примеры, шаблоны, синтаксис и построчные комментарии (zip/xlsx, 260 КБ).

Пример: как автоматизировать SQL запрос к таблицам Excel

Рассмотрим пример розничного магазина. Предположим, вам необходимо подготовить отчёт о продажах за квартал. Вы можете воспользоваться надстройкой SQL Запросы и выполнить запрос к исходным данным. Но если вам приходится готовить подобный отчёт регулярно, этот SQL запрос можно автоматизировать.

1. Подготовьте исходные данные к SQL запросу. Примените к ним формат таблицы, иначе SQL не сможет обработать данные:

Выберете диапазон «Журнал данных прайс-листа и продаж».
На вкладке «Главная» нажмите «Форматировать как таблицу» > Примените стиль таблицы.
На вкладке «Конструктор» присвойте таблице имя «Продажи2014».

2. Добавьте новый лист, напр., «АвтоКоманды», и создайте таблицу команды автоматизации SQL:

XLTools.SQLSelect - введите название команды в точности так. Название должно находиться в двух объединённых ячейках.
SQLQuery - наберите команду запроса привычном образом. Обратите внимание: надстройка использует синтаксис SQLite .
Совет: вместо печати текста запроса вручную, используйте интуитивный редактор SQL Запросов и скопируйте скрипт в таблицу автоматизации.
ApplyTableName - введите название таблицы результата.
Результат запроса автоматически генерируется в формате таблицы. При необходимости к ней можно создавать последующие запросы.
OutputTo - укажите, куда следует поместить результат запроса.

Внимание: чтобы SQL распознавал все ссылки, не используйте пробелы в названиях рабочих листов, книг и таблиц.

3. Выполните команду автоматизации SQL:

Выделите диапазон команды автоматизации > Нажмите кнопку «Выполнить команды» на вкладке XLTools.
Готово, результат сгенерируется в секунды.
В данном примере SQL запрос извлёк данные за 3 квартал 2014.

4. Создайте собственные кнопки на панели инструментов Excel.

В каждой книге Excel можно создать до 3-x пользовательских кнопок, привязанных к вашим собственным командам автоматизации:

Нажмите «Создать кнопки» на вкладке XLTools.
Присвойте кнопке имя, напр. «Отчёт по кварталам» > Укажите диапазон таблицы команды.
Нажмите «Сохранить» > Ваша кнопка появится на вкладке XLTools.

Теперь вы можете легко создать квартальный отчёт, нажав всего одну кнопку.

Появились вопросы или предложения? Оставьте комментарий ниже.

Создание программного кода для автоматизации определенных процедур открывает практически безграничные возможности по развитию и применению приложений на основе Excel.

Запись макроса

Первое средство автоматизации, с которым сталкивается пользователь – это, так называемая, запись макроса. После включения этой записи Excel автоматически создает программный код с последовательностью производимых пользователем операций.

Оценивая оригинальность решения, практическая польза данной функции, с нашей точки зрения, весьма сомнительна. Дело в том, что повторное использование записанного макроса без внесения изменений за редким исключением невозможно. Неподготовленный пользователь обычно видит просто набор команд, но не получает ожидаемый результат от записанного макроса. К тому же у пользователя складывается ошибочное мнение о простоте написания программ на VBA.

Реальное программирование не имеет с записью макросов ничего общего. Требуется знать как основы языка программирования, так и изучить объектную модель Excel. Кстати, как раз для последнего случая функция «запись макроса» несет определенную пользу в качестве дополнения к справочной системе. В автоматически записанном коде можно быстро найти нужные свойства или методы того или иного объекта Excel.

Макросы Excel 4.0

С этим мало, кто сталкивался на практике, но, в версии Excel 4.0 имелась возможность создавать макрокоманды и выполнять их последовательности. Для этого предназначен специальный тип листа рабочей книги. Макросы Excel 4.0 (XLM) поддерживаются во всех версиях до настоящего времени.

Попробуйте вставить «международный лист макросов» и написать в нем 2 команды (см. картинку). Затем запустить этот макрос через указание ячейки (проще всего запуск макросов вызывается через сочетание клавиш Alt+F8 ):

«Международный лист макросов» необходим для записи команд на английском языке, имеются также аналоги русских команд.

Макросы XLM позволяют выполнять любые команды Excel, реализованные на тот момент, а также формировать и открывать диалоговые окна. С конца 90х годов Microsoft настоятельно рекомендует переписать XLM-макросы в Excel-приложениях на программы VBA. Тем не менее, функциональность старых макросов полностью поддерживается до сих пор.

Visual Basic for Applications 5.0

Первая версия Excel, поддерживающая язык программирования Visual Basic for Applications появилась в 1993м году – Excel 5.0. Первоначально редактор кода представлял собой специальный тип листа рабочей книги.

Поддерживалась полная объектная модель Excel, пользователь мог разделять код на программные модули. Впервые появилась возможность визуального создания и изменения диалоговых форм - коллекция DialogSheets . Для данных форм существуют элементы управления с собственными обработчиками событий, сами формы могут быть открыты только в модальном режиме. С помощью DialogSheets уже можно было создавать достаточно сложные варианты пользовательского интерфейса. В дальнейшем функциональность DialogSheets была заменена на пользовательские формы UserForms . Старые диалоговые формы в версиях, начиная с Excel 2000, поддерживаются только в целях обратной совместимости – на них даже отсутствует описание в справочной системе.

VBA 6

Excel 2000 (порядковый номер версии 9.0) включает в себя новую версию языка Visual Basic for Applications. В нем появилась поддержка пользовательских классов, форм и процедур обработчиков событий. Синтаксис языка VBA унифицирован с основными приложениями Microsoft Office (Excel, Word, Access, PowerPoint). Несколько выделяется, пожалуй, Microsoft Access, который поддерживает собственный редактор форм.

Основные новшества VBA6:

Пользовательские классы.
Доступ к стандартным обработчикам событий листов и рабочей книги.
Возможность обработки событий с использованием ключевого слова WithEvents .
Пользовательские формы UserForms с новыми элементами управления.
Возможность использования внешних ActiveX-контролов и библиотек кода.
Несколько новых полезных функций (например, Join и Split ).

Кроме того, на основе новой версии VBA существовали специальные версии Excel для разработчиков, поддерживающие создание COM DLL. Подробнее см.статью Надстройки .

Использование внешних библиотек

Внешние библиотеки формата COM DLL (ActiveX DLL) подключаются через использование ссылок в проекте VBA (меню Tools\References ). Визуальные элементы управления (.ocx-файлы) подключаются аналогичным способом при выборе пункта меню Tools \ Additional Controls . При использовании ссылок на внешние файлы может потребоваться уделить особое внимание целостности проекта при его распространении на другие компьютеры. Стандартные библиотеки (например, MSCOMCTL.OCX) обычно корректно переподключаются на клиентских компьютерах, автоматически подбирая ссылку на последнюю версию. С нестандартными библиотеками и собственными ActiveX-контролами могут возникнуть проблемы. Для проверки подключения внешних COM DLL теоретически можно использовать коллекцию Application.VBE.ActiveVBProject.References . Но на практике объект VBE лучше не использовать в приложениях, так как доступ к нему может быть ограничен настройками Excel (начиная с версии 2007).

Технология COM позволяет обеспечить доступ к внешним библиотекам и приложениям без использования жестких ссылок. Для этого используется, так называемое, позднее связывание объектов. В качестве интерфейса IUnknown в VBA выступает тип Object ; для создания экземпляра объекта используется стандартная процедура CreateObject , а для получения имеющегося экземпляра – GetObject .

Dim oWord As Object Set oWord = CreateObject("Word.Document") oWord.Application.Visible = True

Для вызова функций и процедур динамических библиотек Windows (Win32 DLL) в любой версии VBA поддерживается конструкция Declare . Например:

Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal sBuf As String, ByRef iSize As Long) As Boolean

После подобного объявления функцию можно вызывать обычным способом как любую функцию VBA.

Кроме стандартных Win API процедур и функций, можно разрабатывать собственные DLL и подключать их в проекты VBA аналогичным способом.

VSTO

Microsoft предоставляет средства автоматизации Excel в других своих продуктах для разработчиков. Так, существует, например, шаблон проекта ActiveX DLL Visual Basic 6.0, адаптированный для Microsoft Office. В настоящее время Microsoft поддерживает направление разработки приложений, основанное на технологии.NET - Visual Studio Tools for Office (VSTO). VSTO представляет из себя набор шаблонов проектов и средств поддержки выполнения Visual Studio .NET, предназначенных для разработки библиотек и надстроек различных приложений Microsoft Office.

Пример исходного кода проекта.NET для Excel можно найти в разделе Прочее .

Автоматизация Excel для внешних приложений

Подобно вызову внешних COM DLL, Excel сам может являться объектом автоматизации для других программ. Например, вы можете вызывать и управлять рабочей книгой с помощью vbs-скриптов. Например, создайте текстовый файл следующего содержания:

Set oExcelApp = CreateObject("Excel.Application") oExcelApp.Visible = True Set oWorkbook = oExcelApp.Workbooks.Add Set oWorksheet = oWorkbook.Worksheets.Add oWorksheet.Cells(1,1)="Hello"

Сохраните этот файл с расширением vbs, а затем откройте. При успешном запуске будет открыт Excel, создана рабочая книга и лист, на котором появится текст «Hello» в верхней левой ячейке. Аналогичным образом можно создавать очень сложные программы, управляющие Excel-файлами. При переносе кода в vbs-скрипт (или другое приложение) первоначально удобнее его создавать и отлаживать код в редакторе VBA самого Excel – в нем имеется полная информация о синтаксисе методов и свойств встроенных объектов. В связи с этим хорошей практикой при программировании на VBA является использование полных путей доступа к объектам: например, вместо ActiveWorkbook прописывать Application.ActiveWorkbook . Такой стиль программирования избавит вас от скрытых ошибок при переносе кода в любые другие приложения.

Безопасность файлов

Microsoft, проявляя заботу о пользователях, всячески пытается предотвратить случайное распространение компьютерных вирусов, написанных на VBA. Основываясь на собственном опыте, можем предположить, что такая «забота» зачастую приносит больше проблем пользователям, чем повышает безопасность их работы. В нашей практике работы с файлами Excel (начиная с 1995го года) нам ни разу не встречались макро-вирусы , написанные специально для приложений Excel. При этом мы не исключаем потенциальную возможность написания таких вирусов (даже можем попытаться что-то подобное создать), только непонятно зачем это кому-нибудь нужно. Использование тех же скриптовых языков гораздо более серьезная угроза для операционной системы. Не говоря уже о том, что Visual Basic – это не самое подходящее средство для написания компьютерных вирусов.

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

Вот как исторически изменялись характеристики безопасности:

Excel 4.0-7.0 – нет полной уверенности, но похоже, что не было защиты от макровирусов, макросы запускались без дополнительных предупреждений.
Excel 97 (8.0 ) – появился флажок защиты в общих параметрах.

Excel 2000 (9.0) – появился специальный диалог в меню Сервис \ Макросы с выбором одного из трех уровней безопасности и списком надежных сертификатов. По умолчанию установлен средний уровень безопасности. То есть при открытии файлов с макросами пользователю самому предлагается решить, насколько это безопасно. Надстройки Excel по умолчанию активизируются без дополнительных предупреждений.

Excel 2002 (10.0) – добавился еще один «самый высокий» уровень безопасности, разрешающий запуск макросов, подписанных надежным издателем, и только из надежных источников. По умолчанию установлен средний уровень безопасности.
Excel 2003 (11.0) – то же, что в предыдущей версии, но по умолчанию устанавливается высокий уровень безопасности. То есть макросы отключаются без предупреждений.

Excel 2007 (12.0) – переписан интерфейс параметров, появился так называемый «Центр управления безопасностью». Уровни безопасности изменились по поведению, хотя их также осталось четыре. По умолчанию при открытии файла с макросами под лентой появляется окошко с возможностью активизировать макросы. Условно говоря, это тот же диалог подключения макросов, но открывается он немодально. Решение с одной стороны кажется привлекательным по интерфейсу, но может привести к несовместимости со старым версиями: например, пользователь может поработать некоторое время без макросов, а затем их подключить. Появилась отдельная опция возможности управления объектом VBE на уровне кода, которая по умолчанию отключена. Также отдельно настраивается безопасность надстроек Excel, которые по умолчанию пока все-таки подключаются без дополнительных вопросов.

Учитывайте эти различия в настройках при разработке и распространении собственных приложений для Excel.

С нашей точки зрения слабым моментом в настройке безопасности является то, что все параметры хранятся в системном реестре Windows в открытом виде, и теоретически не составляет особого труда изменять их при работе других приложений. Зачастую этим занимаются инсталляционные программы для приложений Excel.

Сертификаты VBA

Начиная с Microsoft Office 2000 стала доступна технология Microsoft Authenticode, позволяющая подписывать исполняемый код VBA в макросах и надстройках. Для разработчика решений это означает возможность запуска программ с установленным уровнем безопасности Высокий и Очень высокий .

Цифровые подписи обеспечивают поддержку целостности распространяемых решений (никто не может внести изменения в подписанный код, кроме владельца сертификата) и удостоверяют личность разработчика (однозначно определяют автора решения).

Существует несколько вариантов получения сертификатов:

Самоподписанный сертификат
Сертификат из локального центра сертификации
Коммерческий сертификат

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

В случае наличия локального центра сертификации (в рамках организации, например), возможно создание сертификатов, выданных этим центром. При наличии Active Directory возможно распространение корневого сертификата на компьютеры пользователей, которое позволит определять подписанный код как доверенный. Для создания локального центра сертификации можно использовать службы сертификации Windows Server (Certificate Services), либо использовать решения на базе пакета OpenSSL.

При необходимости наиболее широкого распространения решений на базе Microsoft Office, рекомендуется приобрести коммерческий сертификат для подписывания кода (Code Signing). Использование широкоизвестных центров сертификации позволит пользователям быть уверенным, что они получают продукт от вполне определенного поставщика и в неизменённом виде.

Однако, есть несколько моментов в работе с сертификатами, о которых необходимо упомянуть:

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

Для автоматизации трудоемких или часто повторяющихся задач в Excel используются макросы. Макрос - это последовательность команд и действий, сохраненная под одним именем. Макрос можно создать двумя способами: 1) записать действия автоматически; 2) разработать процедуру в редакторе VBA.

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

Для работы с макросами и элементами управления используется закладка Разработчик (рис. 7.1). Если такой закладки на ленте инструментов нет, ее нужно включить следующим образом:

Нажать кнопку Office , щелкнуть по кнопке ;

Установить флажок Показывать вкладку “Разработчик” на ленте .

Запись макроса начинается после нажатия кнопки Запись макроса. Способ записи определяется состоянием кнопки Относительные ссылки . Если кнопку включить, то будет записываться макрос с относительными ссылками, если выключить - с абсолютными.

Рис. 7.1. Закладка Разработчик ленты инструментов

Выполнение макроса можно задать комбинации клавиш, кнопке на панели инструментов, элементу управления формы или графическому объекту на рабочем листе.

Удаление макроса производится в диалоге Макросы , который открывается кнопкой Макросы на закладке Разработчик .Нужно выбрать в списке требуемый макрос и нажать кнопку Удалить . В этом же диалоге можно запустить выбранный макрос на выполнение, если не задан другой способ выполнения макроса.

Для автоматизации заполнения шаблонов в Excel используются элементы управления формы и элементы ActiveX. Элемент управления - это графический объект, позволяющий пользователю управлять приложением. Чтобы создать элемент управления, нужно раскрыть кнопку Вставить (рис. 7.1), выбрать требуемый элемент и растянуть мышью до желаемого размера в нужном месте рабочего листа. Когда элемент управления выделен, с помощью кнопки Свойства на закладке Разработчик можно изменять его параметры (рис. 7.6), например, связать с какой-либо ячейкой листа.

Рис. 7.2. Элементы управления формы

1. Группа - рамка, которая используется для объединения переключателей.

2. Кнопка - используется для выполнения назначенного ей макроса.

3. Флажок - если установлен, то в связанной с ним ячейке выводится значение ИСТИНА, если снят - ЛОЖЬ.

4. Переключатель - используется всегда в группе. Когда переключатели объединены в группу, только один из них может установлен. Тогда в ячейке, связанной с этой группой переключателей, выводится порядковый номер выбранного переключателя.

5. Список (а) и Поле со списком (б) - отображают список значений, который нужно предварительно ввести в ячейки, а затем указать диапазон этих ячеек в параметрах элемента управления. Номер выбранного в списке значения помещается в ячейку, связанную со списком.

6. Полоса прокрутки (а) и Счетчик (б) - изменяют значение связанной с ними ячейки. В параметрах элемента управления можно задать диапазон и шаг изменения этого значения.

Пример 7.1. Записать макрос под именем «Первый_день_месяца», который начиная с текущей ячейки выводит даты первых дней шести месяцев, следующих за текущим, и форматирует их так, чтобы название месяца выводилось словом, выравнивание по левому краю, цвет текста - синий, шрифт - Courier New полужирный.Выполнение макроса назначить автофигуре.

Выполнение:

1. Нажать кнопку Запись макроса на закладке Разработчик .

2. В открывшемся диалоге задать имя макроса - Первый_день_месяца , можно задать комбинацию клавиш и затем нажать ОК. При этом включится запись и кнопка Запись макроса будет преобразована в кнопку Остановить запись (рис. 7.3).

Рис. 7.3. Вид фрагмента закладки Разработчик во время записи макроса

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

В текущую ячейку ввести формулу, которая будет возвращать дату первого числа месяца, следующего за текущим (текущей является дата 15.05.06): =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)

Выделить 6 ячеек, начиная с введенной формулы, раскрыть кнопку Заполнить на закладке Главная и выбрать команду Прогрессия… ;

В открывшемся диалоге указать Тип à Даты, Единицы à Месяц.

Открыть диалог Формат ячеек… ;

На закладке Число задать формат ДД ММММ ГГГГ, на закладке Выравнивание à по горизонтали по левому краю, на закладке Шрифт à шрифт - Courier New полужирный, цвет - синий.

5. Нажать кнопку Остановить запись (рис. 7.3).

6. На рабочем листе нарисовать какую-либо автофигуру, выбрав ее на закладке Вставка . В контекстном меню автофигуры выбрать команду Назначить макрос . В открывшемся диалоге указать макрос «Первый_день_месяца» и нажать ОК.

Рис. 7.4. Результат выполнения макроса «Первый_день_месяца»

7. Сделать текущей любую ячейку и проверить работу макроса щелкнув по автофигуре (рис. 7.4).

Пример 7.2. Создать бланк заказ-наряда (рис. 7.5), в котором наименование работы выводится в ячейке В2 с помощью списка, а количество часов в ячейке В3 заполняется с помощью полосы прокрутки.

Рис. 7.5. Создание бланка с элементами управления формы.

Выполнение:

Заполнить ячейки данными как на рис. 7.5, кроме ячеек В2, В3, Е1.
Нарисовать список (рис. 7.2, 5-а) и полосу прокрутки (рис. 7.2, 6-а).
Изменить параметры созданных элементов управления (рис. 7.6). Так как полоса прокрутки связана с ячейкой В3, то щелчок мышью по стрелке полосы прокрутки будет менять значение ячейки на один шаг - 1. Список связан с ячейкой Е1, поэтому при выборе значения «Побелка потолка» в ячейке будет выведен порядковый номер этого значения в списке - 3.

Рис. 7.6. Параметры полосы прокрутки (слева) и списка (справа).

Чтобы вывести в В2 наименование работы, а не ее номер, нужно использовать функцию ВПР, которая в зависимости от номера выбранной из списка работы в ячейке Е1, будет возвращать соответствующее значение из второго столбца таблицы Е2:F5. Таким образом, формула в В2 будет следующей: =ВПР(E1;E2:F5;2).

Замечание . Полосу прокрутки и список можно создать с помощью элементов ActiveX и затем изменить их свойства, как показано на рисунке:

Лабораторная работа № 7

Цель работы: научиться создавать и использовать макросы и элементы управления формы на рабочем листе для автоматизации работы в Excel.

Задания:

I. Записать макросы:

С относительными ссылками. Макрос должен выводить названия месяцев в столбце, начиная с текущей ячейки, со следующими элементами форматирования:

Цвет символов - красный,

Обрамление ячеек - тонкая линия,

Текст выровнен по центру,

Внешнее обрамление столбца - жирная линия.

На панели элементов Формы выбрать элемент Кнопка , нарисовать его на рабочем листе, назвать МЕСЯЦЫ и назначить созданный макрос.

С абсолютными ссылками. Макрос должен очищать весь рабочий лист. На рабочем листе нарисовать любую автофигуру и назначить созданный макрос.

3. Ввести в какую-нибудь ячейку любую дату. Макрос должен, начиная с введенной даты, заполнять 10 ячеек датами по рабочим дням, очищать 10 ячеек двух соседних столбцов и форматировать их так, как показано на рисунке. На рабочем листе нарисовать любую автофигуру и назначить созданный макрос.

II. Создать шаблон для заполнения бланка заказа авиабилетов с элементами управления формы:

1. Заполнить ячейки данными, кроме ячеек С2:С4, С6 и С13, и вставить элементы управления формы:

2. Изменить свойства элементов управления:

2.1. Для поля со списком вывод на печать отключен, объемное затенение включено, список формируется по диапазону I3:I7 , результат помещается в ячейку G3 , т. е. в эту ячейку помещается номер элемента, который был выбран в списке.

2.2. Для счетчика вывод на печать отключен, объемное затенение включено, диапазон изменения от 1 до 10 с шагом 1, результат помещается в ячейку С6 .

2.3. Для переключателей вывод на печать и объемное затенение включены, результат помещается в ячейку G4 . Переключатели объединены в рамку .

2.4. Для кнопки вывод на печать отключен.

3. В ячейках С2:С4 написать формулы (используя функцию ВПР), которые выводят город, время вылета и цену билета в зависимости от выбранного значения списка.

4. В ячейке С13 написать формулу для расчета суммы к оплате в зависимости от количества билетов и типа билета (для льготного - скидка 30%).

5. Снять защиту с ячеек, с которыми связаны элементы управления.

6. Формулы скрыть.

7. Скрыть столбцы, содержащие вспомогательные данные.

8. Создать и назначить кнопке «Печать » макрос, который задает альбомную раскладку и размер бумаги А5 и отображает шаблон в режиме предварительного просмотра.

Контрольные вопросы к теме

1. Какими способами можно автоматизировать заполнение шаблона?

3. Назовите элементы управления и их назначение.

4. Как создать элемент управления на рабочем листе и изменить его параметры?

5. Назовите способы выполнения макроса.

Литература

1. Microsoft Office 2007: все программы пакета: Word, Excel, Access, PowerPoint, Publisher, Outlook, OneNote, InfoPath, Groove / Тихомиров А. Н. [и др.]. - Санкт-Петербург: Наука и техника, 2009. - 599 с.

2. Microsoft Office Excel 2007: [русская версия: перевод с английского] / Кёртис Д. Фрай. - Москва: ЭКОМ, 2009. - 479 с.

3. Excel 2007 для менеджеров и экономистов: логистические, производственные и оптимизационные расчеты / Александр Трусов. - Санкт-Петербург: Питер: Питер Пресс, 2009. – 254 с.

4. Графики, вычисления и анализ данных в Excel 2007 / Серогодский В. В. [и др.]. - Санкт-Петербург: Наука и техника, 2009. - 333 с.

5. Функции в Excel 2007: справочник пользователя / Минько А. А.. - Москва: Эксмо, 2008. - 480 с.

6. Экономическая информатика / под ред. П.В. Конюховского и Д.Н. Колесова. – СПб: Питер, 2001. – 560 с.

7. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: BHV–Петербург, 2001. – 816 с.

8. Додж М., Кината К., Стинсон К. Эффективная работа с Excel 7.0: пер. с англ. – СПб: Питер, 1996. – 1031 с.

9. Обработка и анализ экономической информации в Microsoft Excel: Пособие для студентов экономических специальностей / Д.П. Подкопаев, В.И. Яшкин. – Мн.: Издательство БГУ, 2001. – 50 с.

10. Бизнес-анализ с помощью Microsoft Excel / Карлберг Конрад. – 2-е изд. – М.: Вильямс, 2003. – 446 с.

11. Использование макросов в Excel / С. Роман. – 2-е изд. – СПб: Питер, 2004. – 507 с.

12. Гетц К., Гилберт М. Программирование на Visual Basic и VBA. Руководство разработчика: пер с англ. – К.: Издательская группа BHV, 2001. – 912 с.

Для автоматизации работы приложений часто используются макросы. Любой макрос — это последовательность действий, записанная под определенным именем. Если при работе с Microsoft Excel возникает необходимость несколько раз выполнить одну и ту же последовательность операций (например, сложное форматирование текущей ячейки или добавление новой строки с заполнением некоторых ее ячеек формулами), то можно записать эти действия, а затем воспроизводить их столько раз, сколько потребуется.

Записанный макрос можно вызывать для выполнения с помощью специального диалогового окна или при помощи комбинации клавиш. Кроме того, макрос можно назначить кнопке на панели быстрого доступа. В общем случае, для того чтобы записать макрос, не нужно быть программистом — достаточно просто знать, как выполнить команды, приводящие к нужному результату.

Вкладка Разработчик , позволяющая записывать макросы, по умолчанию скрыта. Для того чтобы ее показать, необходимо выбрать Office → Параметры Excel → Основные и использовать опцию Показывать вкладку «Разработчик» на ленте . После установки этого флажка на ленте появится вкладка Разработчик .

Запись макросов

Для того чтобы записать макрос, необходимо:

Детально представить ту последовательность действий, которую нужно выполнять (во избежание возможных ошибок, лишних или неправильных действий).
Выбрать элемент, к которому будут применяться действия, записываемые в макрос (установить курсор в пределах ячейки и пр.).
Вызвать диалоговое окно Запись макроса одним из двух способов:
- нажать кнопку Запись макроса на панели Код вкладки Разработчик;
- нажать кнопку Запись макроса в левой части строки состояния Excel.
Ввести в поле Имя макроса название создаваемого макроса. При этом первым символом имени макроса должна быть буква. Остальные символы могут быть как буквами, так и цифрами или знаком подчеркивания. В имени не допускаются пробелы; в качестве разделителей слов рекомендуется использовать знаки подчеркивания.
В выпадающем списке Сохранить в указать, где будет сохранен данный макрос. Возможные варианты:
- Личная книга макросов — макрос сохраняется в специальный файл, который будет доступен для всех документов сразу после загрузки Excel;
- Новая книга — будет создана новая книга, и макрос будет сохранен в ней. Этот макрос будет доступен только в том случае, если содержащая его книга будет открыта;
- Эта книга — макрос будет сохранен в текущей книге. Обычно такие макросы необходимы, когда их использование ограничено мелкой автоматизацией в текущей книге. Тем не менее, макрос будет доступен из других документов, если они будут открыты параллельно.
Если необходимо, чтобы макрос вызывался комбинацией клавиш, ввести букву или цифру в поле Сочетание клавиш .

Если вводится буква, то важен как регистр, так и раскладка. Например, если вводится буква д (малая русская), то комбинация клавиш для этого макроса будет Ctrl+д , но не Ctrl+I (малая английская L, которая находится на той же клавише).

Нажать кнопку OK , затем кнопку Запись макроса на панели Код вкладки Разработчик , которая в строке состояния превратится в кнопку Остановить запись , останавливающую запись макроса.
Выполнить последовательность действий, которые будут записаны в макрос.

Все значимые действия пользователя (щелчки по кнопкам, редактирование данных, перемещение по листу и т.д.) будут записаны. Сделать паузу в записи невозможно.

Завершить запись макроса нажатием кнопки Остановить запись . По окончании записи макроса он появляется в списке макросов иод своим именем.

Выполнение макросов

Для выполнения макроса можно воспользоваться диалоговым окном Макрос . Чтобы отобразить это окно, можно нажать комбинацию клавиш Alt+F8 либо воспользоваться кнопкой Макросы на панели Код . Далее в этом окне необходимо выбрать требуемый макрос и нажать кнопку Выполнить . Второй возможностью выполнить макрос является комбинация клавиш, выбранная на этапе создания макроса. Кроме того, в Excel предусмотрена возможность вызова макроса при помощи кнопки па панели быстрого доступа.

Создание кнопки макроса на панели быстрого доступа

Для создания кнопки на панели быстрого доступа нужно:

Вызвать диалоговое окно Параметры Excel и перейти на вкладку Настройка .
В выпадающем списке Выбрать команды из выбрать пункт Макросы .
Выбрать нужный макрос из списка макросов и нажать кнопку Добавить — он скопируется в правое окно и станет доступен в виде кнопки на панели быстрого доступа.
Чтобы для созданной кнопки изменить рисунок и отображаемое имя, следует воспользоваться командой Изменить .
Нажать кнопку ОК в диалоговом окне Параметры Excel.

В результате кнопка добавится на панель быстрого доступа. Отображаемое имя будет появляться в виде всплывающей подсказки при наведении на кнопку указателя мыши.

Удаление макроса

Для удаления макроса следует нажать кнопку Макросы на панели Код или комбинацию клавиш Alt+F8 — появится диалоговое окно Макрос , содержащее список макросов, доступных в открытых книгах. В данном окне необходимо выбрать удаляемый макрос и нажать кнопку Удалить.

При удалении макроса его кнопка в панели быстрого доступа не удаляется автоматически, поэтому ее нужно удалить вручную на вкладке Настройки диалогового окна Параметры Excel .

Редактирование макроса

Созданный макрос на самом деле представляет собой процедуру, записанную на языке программирования VBA. Ее легко можно увидеть в окне редактора Visual Basic Editor. Подробно о VBA и редакторе Visual Basic Editor рассказано в других наших статьях. Сейчас важно отметить то, что макрос записывается в виде обычного текста, а это значит, что при необходимости его легко можно редактировать.

Для входа в режим редактирования макроса нужно:

В диалоговом окне Макрос выбрать подлежащий редактированию макрос.
Нажать кнопку Изменить — откроется окно Microsoft Visual Basic.

Для примера создадим в текущей книге Excel простейший макрос, который должен применяться к выделенной ячейке и изменять ее форматирование (цвет фона - красный, цвет шрифта — светло-коричневый, Фон 2). Чтобы создать этот макрос, необходимо:

Выделить любую ячейку на листе и ввести в нее какой-нибудь текст (чтобы можно было посмотреть на результат работы).
Включить запись макроса с помощью кнопки Запись макроса в строке состояния.
В диалоговом окне Запись макроса все параметры оставить выбранными по умолчанию и нажать кнопку ОК .
На вкладке Главная (панель Шрифт) выбрать нужные параметры цвета.
Остановить запись макроса при помощи кнопки Остановить запись в строке состояния.

Теперь для того чтобы просмотреть код созданного макроса, необходимо вызвать редактор Visual Basic способом, описанным выше, — откроется окно кода для созданного макроса, в котором будет записан следующий код:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

// Макрос форматирования ячейки Макрос1 Sub Макрос1() "Макрос! Макрос With Selection.Interior .Pattern = xlSolid .PatternColorlndex = xlAutomatic .Color = 255 .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Font .ThemeColor = xlThemeColorDark2 .TintAndShade = 0 End With End Sub

Чтобы при последовательном обращении к нескольким свойствам одного объекта каждый раз не указывать длинную цепочку типа Selection.Interior.Pattern = xlSolid , используется специальная конструкция языка VBA: With…End With — внутри нес имя объекта можно опустить, указывая лишь точку перед именем свойства.

Для тех, кто знает английский язык, назначение и принцип работы команд в данном коде достаточно очевидны: для объекта Selection.Interior (т.е. для внутренней области выделенной ячейки) выполняется задание значений различным параметрам фона. Основные параметры — Pattern (способ заливки, в данном случае задан xlSolid , т.е. сплошным) и Color (Цвет). Аналогичным образом происходит изменение цвета шрифта (начиная со строки With Selection.Font ).

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

Цели полностью удалить код макроса в редакторе Visual Basic, то макрос также будет полностью удален (это аналогично действию кнопки Удалить диалогового окна Макрос ).

Для переименования макроса достаточно войти в режим его редактирования и в тексте программы исправить заголовок в строке Sub Макрос1() — новое имя автоматически заменит старое в списках макросов. Заметим, что не все замены происходят автоматически. Если с макросом были связаны графические объекты или кнопки, то необходимо сделать для них переназначения.

Высокий уровень производства и дохода, являющийся результатом применения передовой технологии и крупных масштабов производства, приводит к тому, что на весьма значительную часть населения перестает давить бремя забот, связанных с удовлетворением элементарных физических потребностей. Ни одного голодного человека, если только он трезв, невозможно убедить в том, чтобы он истратил свой последний доллар на что-либо, кроме еды. Но человека, который хорошо питается, хорошо одет, имеет хорошие жилищные условия и хорошо обеспечен во всех остальных отношениях, можно убедить в том, чтобы он решил купить электробритву или электрическую зубную тетку. Не только цены и издержки производства, но и потребительский спрос становится объектом управления. Таков еще один важный дополнительный элемент в системе регулирования экономической среды…

Настоящая статья посвящена теме, может быть, и не новой, но, как показывают письма читателей, по-прежнему актуальной - автоматизации приложений Microsoft Office. Многие разработчики в процессе работы над своими проектами (неважно, с помощью какого средства разработки - Delphi, C++Builder, Visual Basic:) нередко применяют сервисы, предоставляемые Microsoft Office, например построение сводных таблиц и диаграмм с помощью Microsoft Excel, генерацию и печать документов с помощью Microsoft Word и т.д. Нередко пользователи, привыкшие использовать приложения Microsoft Office в повседневной работе, сами настаивают на применении в приложениях таких сервисов либо просто на сохранении отчетов и других документов в одном из форматов Microsoft Office. Отметим, что потенциальные пожелания подобного рода компанией Microsoft учтены достаточно давно - практически все, что в состоянии сделать пользователь любого приложения Microsoft Office с помощью меню, клавиатуры и инструментальной панели, может быть произведено и автоматически, то есть либо из VBA-программы, либо из приложения, созданного с помощью одного из средств разработки. Иными словами, приложения Microsoft Office являются программируемыми. Программируемость в общем случае означает возможность управления данным приложением с помощью макроязыков либо с помощью других приложений. Все компоненты Microsoft Office поддерживают один и тот же макроязык: Visual Basic for Applications (VBA), позволяющий создавать приложения непосредственно внутри документов Office (это называется <решения на базе Microsoft Office>). Управление же компонентами Office из других приложений осуществляется с помощью автоматизации (Automation, ранее - OLE Automation) - все приложения Microsoft Office являются серверами автоматизации (или COM-серверами). Для создания таких приложений пригодны любые средства разработки, позволяющие создавать контроллеры автоматизации (COM-клиенты). Наиболее часто для этой цели используется Visual Basic, но это могут быть и Delphi, и C++Builder, и Visual C++. Однако прежде чем обсуждать возможности тех или иных средств разработки, следует разобраться, что такое автоматизация.

Коротко об автоматизации

Автоматизация - это одна из возможностей, предоставляемых технологией Microsoft COM (Component Object Model). Не вдаваясь в подробности реализации этой технологии, заметим, что она используется приложениями (называемыми COM-серверами) для предоставления доступа к их объектам, а также к свойствам и методам этих объектов другим приложениям (называемым COM-клиентами), каковыми могут быть и средства разработки. Например, текстовый процессор, будучи COM-сервером, может предоставлять другим приложениям доступ к документу, абзацу, закладке с помощью соответствующих объектов. Для именования (и опознания) COM-серверов обычно используются специальные строковые обозначения - программные идентификаторы (Programmatic Identifier, ProgID). Они нужны для того, чтобы операционная система могла с помощью этих идентификаторов определить, в каком именно каталоге (или на каком компьютере локальной сети, если используется тот или иной способ удаленного доступа к серверу) расположен исполняемый файл сервера автоматизации, и запустить его на выполнение. О том, какие именно программные идентификаторы применяются для того или иного приложения Microsoft Office, будет сказано в разделах, посвященных этим приложениям.

Объекты автоматизации с точки зрения программирования мало чем отличаются от обычных объектов, знакомых нам из теории и практики объектно-ориентированного программирования. Как и обычные объекты, они обладают свойствами и методами. Свойство - это характеристика объекта; например, свойством абзаца (объект Paragraph) может быть его стиль (Style). Методом называется действие, которое можно выполнить с объектом (например, можно сохранить документ с помощью метода SaveAs объекта Document).

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

Как узнать, какие объекты доступны в серверах автоматизации? Для этой цели в комплект поставки любого сервера автоматизации входит документация и файлы справки, описывающие их объектную модель. Если брать приложения Microsoft Office 2000, то это справочные файлы для программистов на Visual Basic for Applications VBAxxx9.CHM (для Microsoft Office 97 - VBxxx8.HLP соответственно). Отметим, что по умолчанию они не устанавливаются, так как нужны разработчикам, а не рядовым пользователям.

Вся информация об объектах, необходимая контроллерам автоматизации, содержится в библиотеках типов. Библиотеки типов хранятся в специальном бинарном формате и обычно представлены в виде файлов с расширениями *.olb или *.tlb, а также могут содержаться внутри исполняемых файлов (*.exe) или динамически загружаемых библиотек (*.dll). Библиотеки типов можно просматривать с помощью утилиты OleView, входящей в состав Microsoft Platform SDK, а средства разработки фирмы Borland содержат свои собственные утилиты просмотра библиотек типов.

Заканчивая это более чем краткое введение в автоматизацию, остановимся также на понятии связывания. Связыванием называется способ, с помощью которого переменная или класс приложения-контроллера ссылается на объект автоматизации. Таких способов существуют два: позднее связывание и раннее связывание.

Позднее связывание означает, что реальная ссылка на объект в переменной или классе появляется на этапе выполнения приложения-контроллера. Этот способ обычно более медленный, чем ранее связывание, но он доступен во всех средствах разработки, позволяющих создавать контроллеры автоматизации, и менее чувствителен к тому, все ли параметры методов перечислены при их вызовах в коде приложения-контроллера. При позднем связывании корректность вызовов методов проверяется в момент их осуществления, то есть на этапе выполнения приложения, а не на этапе его компиляции.

Раннее связывание означает, что реальная ссылка на объект автоматизации появляется уже на этапе компиляции. Поэтому в коде приложения-контроллера требуется ссылка на библиотеку типов (или на специальный модуль, являющийся интерфейсом к ней), и это приводит к генерации в этом приложении классов с теми же свойствами и методами, что и у объектов автоматизации, что делает возможными синтаксическую проверку имен методов на этапе компиляции и доступ к именованным константам, содержащимся в библиотеке типов. Отметим, однако, что далеко не все средства разработки, поддерживающие позднее связывание, осуществляют также поддержку раннего связывания.

Закончив экскурс в COM и автоматизацию, вернемся к вопросу о том, какие средства разработки удобно применять для создания контроллеров автоматизации.

VBA и средства разработки контроллеров автоматизации

Как уже было сказано выше, контроллеры автоматизации можно создавать с помощью различных средств разработки, и если вы уже используете какое-либо из инструментальных средств, поддерживающих создание контролеров автоматизации, можно именно им и воспользоваться. Единственное, что следует сделать - это узнать, поддерживается ли вашим средством разработки ранее связывание, если вы планируете его использовать. Примеры простейших контроллеров автоматизации (как правило, для Microsoft Word или Microsoft Excel), дающие представление о том, как вызываются методы сервера, обычно можно найти в комплекте поставки большинства современных средств разработки.

Освоение объектной модели автоматизируемого сервера можно начать с записи необходимой последовательности действий в виде макроса с помощью VBА. Создать макрос можно, выбрав из меню приложения Microsoft Office пункт Tools | Macro |Record New Macro. Просмотр полученного макроса в редакторе кода VBA обычно позволяет понять, как должен выглядеть код, реализующий эту последовательность действий.

Обсудив возможные средства разработки контроллеров автоматизации, можно наконец перейти к самим приложениям Microsoft Office, к их объектным моделям и к их использованию. В качестве средства разработки для приведенных ниже примеров используется Borland Delphi 5, но поскольку в них приводится последовательность вызова свойств и методов объектов Microsoft Office, перенос кода на другие языки программирования не должен вызвать особых затруднений. Если специально не оговорено, в данной статье во всех примерах используется раннее связывание.

Объектные модели Microsoft Office

Как было сказано выше, приложения Microsoft Office предоставляют контроллерам автоматизации доступ к своей функциональности с помощью своей объектной модели, представляющей собой иерархию объектов. Объекты могут предоставлять доступ к другим объектам посредством коллекций.

В качестве иллюстрации того, как выглядит иерархия объектов Microsoft Office, приведем небольшой фрагмент объектной модели Microsoft Word (рис.1):

В объектных моделях (рис.2)

всех приложений Microsoft Office всегда имеется самый главный объект, доступный приложению-контроллеру и представляющий само автоматизируемое приложение. Для всех приложений Microsoft Office он называется Application, и многие его свойства и методы для всех этих приложений также одинаковы. Наиболее часто мы будем использовать следующие из них:

Свойство Visible (доступное для объекта Application всех приложений Microsoft Office) позволяет приложению появиться на экране и в панели задач; оно принимает значения True (пользовательский интерфейс приложения доступен) или False (пользовательский интерфейс приложения недоступен; это значение устанавливается по умолчанию). Если вам нужно сделать что-то с документом Office в фоновом режиме, не информируя об этом пользователя, можно не обращаться к этому свойству - в этом случае приложение можно будет найти только в списке процессов с помощью приложения Task Manager.
Метод Quit закрывает приложение Office. В зависимости от того, какое приложение Office автоматизируется, он может иметь параметры или не иметь таковых.

Общие принципы создания контроллеров автоматизации

В общем случае контроллер автоматизации должен выполнять следующие действия:

Проверить, запущена ли копия приложения-сервера.
В зависимости от результатов проверки запустить копию автоматизируемого приложения Office либо подключиться к уже имеющейся копии.
Сделать окно приложения-сервера видимым (в общем случае это не обязательно).
Выполнить какие-то действия с приложением-сервером (например, создать или открыть документы, изменить их данные, сохранить документы и пр.)
Закрыть приложение-сервер, если его копия была запущена данным контроллером, или отключиться от него, если контроллер подключился к уже имеющейся копии.

Соответствующий код для Delphi представлен в листинге 1 .

Здесь мы воспользовались функциями GetActiveOleObject и CreateOleObject для подключения к уже запущенной копии приложения-сервера или запуска новой, если сервер не запущен, что приводит к тому, что в вариантную переменную помещается ссылка на объект Application соответствующего сервера.

Приведенная часть кода контроллера в других средствах разработки может выглядеть по-другому - она не имеет прямого отношения к методам объектов Office, так как обусловлена правилами вызова стандартных функций OLE в Delphi (за исключением вызовов методов Visible и Quit объекта Application). А вот все то, что должно быть вставлено вместо комментария <Здесь выполняются другие действия с объектами приложения Office>, в разных средствах разработки должно выглядеть более или менее однотипно - используя созданную вариантную переменную, мы манипулируем методами и свойствами объекта Application.

В заключение сделаем одно маленькое замечание касательно числа параметров методов объектов автоматизации. В случае применения позднего связывания число указанных в коде параметров метода не обязано совпадать с их истинным числом (которое можно найти в описании объектной модели соответствующего приложения). В этом случае вполне допустимым является следующий код:

несмотря на то что метод Quit объекта Application в случае некоторых приложений Microsoft Office (например, Microsoft Word) имеет параметры.

При использовании же раннего связывания, например при создании контроллеров с помощью Delphi, следует более строго подходить к определению параметров - их число и тип должны соответствовать описанию методов в библиотеке типов. Например, в случае раннего связывания корректный код на Delphi для закрытия документа Word со значениями всех параметров по умолчанию будет иметь вид:

App.Quit(EmptyParam, EmptyParam, EmptyParam);

Исключением из этого правила является Visual Basic - это средство разработки позволяет не задумываться о числе параметров методов при написании кода.

Обсудив общие принципы создания контроллеров автоматизации и узнав, как это делается средствами Borland Delphi, мы можем перейти к рассмотрению автоматизации конкретных приложений Microsoft Office. Начнем с одного из самого популярных компонентов этого пакета - с Microsoft Word.

Программные идентификаторы и объектная модель Microsoft Word

Для приложения-контроллера доступны непосредственно следующие объекты:

Все остальные объекты Word являются так называемыми внутренними объектами. Это означает, что они не могут быть созданы сами по себе; так, объект Paragraph (абзац) не может быть создан отдельно от содержащего его документа.

Если вспомнить, что основное назначение Word - работа с документами, можно легко понять иерархию его объектной модели (фрагмент ее был показан на рис. 1). Основным объектом в ней, как иво всех других приложениях Microsoft Office, является объект Application, содержащий коллекцию Documents объектов типа Document. Каждый объект типа Document содержит коллекцию Paragraphs объектов типа Paragraph, Bookmarks типа Bookmark, Characters типа Character и т.д. Манипуляция документами, абзацами, символами, закладками реально осуществляется путем обращения к свойствам и методам этих объектов.

Ниже мы рассмотрим наиболее часто встречающиеся задачи, связанные с автоматизацией Microsoft Word. Если же вам встретилась задача, не совпадающая ни с одной из перечисленных, вы можете попытаться найти подходящий пример на Visual Basic в справочном файле VBAWRD9.CHM, либо, как уже было сказано выше, записать необходимую последовательность действий в виде макроса и проанализировать его код.

Создание и открытие документов Microsoft Word

Для создания примеров использования Microsoft Word можно использовать код создания контроллера, приведенный выше, и модифицировать его, заменяя комментарии кодом, манипулирующим свойствами и методами объекта Word.Application. Мы начнем с создания и открытия документов.

Создать новый документ Word можно, используя метод Add коллекции Documents объекта Application:

App.Documents.Add;

Как создать нестандартный документ? Очень просто - нужно указать имя шаблона в качестве параметра метода Add:

App.Documents.Add("C:\Program Files\ _ Microsoft Office\Templates\1033\Manual.dot");

Для открытия уже существующего документа следует воспользоваться методом Open коллекции Documents:

App.Documents.Open("C:\MyWordFile.doc");

Отметим, что свойство ActiveDocument объекта Word.Application указывает на текущий активный документ среди одного или нескольких открытых. Помимо этого к документу можно обращаться по его порядковому номеру с помощью метода Item; например ко второму открытому документу можно обратиться так:

App.Documents.Item(2)

Отметим, что нумерация членов коллекций в Microsoft Office начинается с единицы.

Сделать документ активным можно с помощью метода Activate:

App.Documents.Item(1).Activate;

Следующее, чему следует научиться - это сохранять документ Word и закрывать сам Word.

Сохранение, печать и закрытие документов Microsoft Word

App.Documents.Item(2).Close;

App.ActiveDocument.Close ;

Метод Close имеет несколько необязательных (в случае позднего связывания) параметра, влияющих на правила сохранения документа. Первый из них влияет на то, сохраняются ли внесенные в документ изменения, и принимает три возможных значения (соответствующие константы рекомендуется описать в приложении. См. листинг 2):

Третий параметр принимает значения True или False и влияет на то, пересылать ли документ следующему пользователю по электронной почте. Если эта функциональность не применяется, можно проигнорировать этот параметр.

Таким образом, при использовании этих параметров закрыть документ можно, например, так:

App.ActiveDocument.Close(wdSaveChanges, _ wdPromptUser) ;

App.ActiveDocument.Save;

Этот метод также имеет несколько необязательных (в случае позднего связывания) параметров, первый из которых равен True, если документ сохраняется автоматически, и False, если нужно выводить диалоговую панель для получения подтверждения пользователя о сохранении изменений (если таковые были сделаны). Второй параметр влияет на формат сохраняемого документа, и список его возможных значений совпадает со списком значений второго параметра метода Close.

Напоминаем, что закрыть сам Word можно с помощью метода Quit объекта Word.Application. Этот метод имеет в общем случае три параметра, совпадающих с параметрами метода Close объекта Document.

Вывод документа на устройство печати можно осуществить с помощью метода PrintOut объекта Document, например:

App.ActiveDocument.PrintOut;

Если нужно изменить параметры печати, следует указать значения соответствующих параметров метода PrintOut (в случае Microsoft Word их около двадцати).

Вставка текста и объектовв документ и форматирование текста

Для создания абзацев в документе можно использовать коллекцию Paragraphs объекта Document, представляющую набор абзацев данного документа. Добавить новый абзац можно с помощью метода Add этой коллекции:

App.ActiveDocument.Paragraphs.Add;

Для вставки собственно текста в документ, тем не менее, применяется не объект Paragraph, а объект Range, представляющий любую непрерывную часть документа (в том числе и вновь созданный абзац). Этот объект может быть создан разными способами. Например, можно указать начальный и конечный символы диапазона (если таковые имеются в документе):

Var Rng: Variant; ... Rng:= App.ActiveDocument.Range(2,4); //со 2-го по 4-й символы

или указать номер абзаца (например, только что созданного):

Rng:= App.ActiveDocument.Paragraphs.Item(1).Range;

или указать несколько абзацев, следующих подряд:

Rng:= App.ActiveDocument.Range _ (App.ActiveDocument.Paragraphs.Item(3).Range.Start,_ App.ActiveDocument.Paragraphs.Item(5).Range.End)

Вставить текст можно с помощью методов объекта Range InsertBefore (перед диапазоном) или InsertAfter (после диапазона), например:

Rng.InsertAfter("Это вставляемый текст");

Помимо объекта Range текст можно вставлять с помощью объекта Selection, являющийся свойством объекта Word.Application и представляющий собой выделенную часть документа (этот объект создается, если пользователь выделяет часть документа с помощью мыши, и может быть также создан с помощью приложения-контроллера). Сам объект Selection можно создать, применив метод Select к объекту Range, например:

Var Sel: Variant; ... App.ActiveDocument.Paragraphs.Item(3).Range.Select;

В приведенном выше примере в текущем документе выделяется третий абзац.

Если мы хотим вставить строку текста в документ либо вместо выделенного фрагмента текста, либо перед ним, это можно сделать с помощью следующего фрагмента кода:

Var Sel: Variant; ... Sel:= App.Selection; Sel.TypeText("Это текст, которым мы заменим _ выделенный фрагмент");

Отметим, что если свойство Options.ReplaceSelection объекта Word.Application равно True, выделенный текст будет заменен на новый текст (этот режим действует по умолчанию); если же нужно, чтобы текст был вставлен перед выделенным фрагментом, а не вместо него, следует установить это свойство равным False:

App.Options.ReplaceSelection:= False;

Символ конца абзаца при использовании объекта Selection может быть вставлен с помощью следующего фрагмента кода:

Sel.TypeParagraph;

К объекту Selection, так же как и к объекту Range, можно применить методы InsertBefore и InsertAfter. В этом случае, в отличие от предыдущего, вставляемый текст станет частью выделенного фрагмента текста.

С помощью объекта Selection, используя его свойство Font и свойства объекта Font, такие как Bold, Italic, Size,:, можно отформатировать текст. Например, таким образом можно вставить строку, выделенную жирным шрифтом:

Sel.Font.Bold:= True; Sel.TypeText("Это текст, который мы выделим _ жирным шрифтом."); Sel.Font.Bold:= False; Sel.TypeParagraph;

Для наложения на вставляемый текст определенного заранее стиля можно использовать свойство Style этого же объекта, например:

Sel.Style:= "Heading 1"; Sel.TypeText("Это текст, который станет _ заголовком"); Sel.TypeParagraph;

Нередко документы Word содержат данные других приложений. Простейший способ вставить такие данные в документ - использовать метод Paste объекта Range:

Var Rng: Variant; ... Rng:= App.Selection.Range; Rng.Collapse(wdCollapseEnd); Rng.Paste;

Естественно, в этом случае в буфере обмена уже должны содержаться вставляемые данные.

Если нужно поместить в буфер обмена часть документа Word, это можно сделать с помощью метода Copy объекта Range:

Var Rng: Variant; ... Rng:= App.Selection.Range; Rng.Copy;

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

Перемещение курсора по тексту

Используя метод Collapse, можно <сжать> объект Range или объект Selection, сократив его размер до нуля символов:

Rng.Collapse(wdCollapseEnd);

Параметр этого метода указывает, в начале или в конце исходного фрагмента окажется новый объект Range или Selection. Если вы используете позднее связывание и ваше средство разработки - не Visual Basic, нужно определить в приложении соответствующие константы:

Const wdCollapseStart = $00000001; //новый объект находится в начале фрагмента wdCollapseEnd = $00000000; //новый объект находится в конце фрагмента

Перемещать курсор по тексту можно с помощью метода Move объектов Range и Selection. Этот метод имеет два параметра. Первый указывает на то, в каких единицах измеряется перемещение - в символах (по умолчанию), словах, предложениях, абзацах и др. Второй параметр указывает, на сколько единиц при этом нужно переместиться (это число может быть и отрицательным; по умолчанию оно равно 1). Например, следующий фрагмент кода:

Rng.Move;

приведет к перемещению курсора на один символ вперед, а

Rng.Move(wdParagraph,3);

приведет к перемещению курсора на три абзаца вперед. Отметим, что этот метод использует следующие константы:

Const //Единицей перемещения является: wdCharacter = $00000001; //символ wdWord = $00000002; //слово wdSentence = $00000003; //предложение wdParagraph = $00000004; //абзац wdStory = $00000006; //часть документа //напр., колонтитул, //оглавление и др.) wdSection = $00000008; //раздел wdColumn = $00000009; //колонка таблицы wdRow = $0000000A; //строка таблицы wdCell = $0000000C; //ячейка таблицы wdTable = $0000000F; //таблица

Нередко для перемещения по тексту используются закладки. Создать закладку в текущей позиции курсора можно путем добавления члена коллекции Bookmarks объекта Document c помощью метода Add, указав имя закладки в качестве параметра, например:

App.ActiveDocument.Bookmarks.Add("MyBookmark");

Проверить существование закладки в документе можно с помощью метода Exists, а переместиться на нее - с помощью метода Goto объектов Document, Range или Selection:

Rng:= App.ActiveDocument.Goto(wdGoToBookmark, wdGoToNext,"MyBookmark"); Rng.InsertAfter("Текст, вставленный после закладки");

Значения констант для этого примера таковы:

WdGoToBookmark = $FFFFFFFF; //перейти к закладке wdGoToNext = $00000002; //искать следующий объект в тексте

Отметим, что с помощью метода Goto можно перемещаться не только на указанную закладку, но и на другие объекты (рисунки, грамматические ошибки и др.), и направление перемещения тоже может быть различным. Поэтому список констант, которые могут быть использованы в качестве параметров данного метода, довольно велик.

Создание таблиц

Создавать таблицы можно двумя способами. Первый заключается в вызове метода Add коллекции Tables объекта Document и последовательном заполнении ячеек данными. Этот способ при позднем связывании работает довольно медленно.

Второй способ, намного более <быстрый>, заключается в создании текста из нескольких строк, содержащих подстроки с разделителями (в качестве разделителя можно использовать любой или почти любой символ, нужно только, чтобы он заведомо не встречался в данных, которые будут помещены в будущую таблицу), и последующей конвертации такого текста в таблицу с помощью метода ConvertToTable объекта Range. Ниже приведен пример создания таблицы из трех строк и трех столбцов этим способом (в качеcтве разделителя, являющегося первым параметром метода ConvertToTable, используется запятая):

Var Rng: Variant; ... Rng:= App.Selection.Range; Rng.Collapse(wdCollapseEnd); Rng.InsertAfter("1, 2, 3"); Rng.InsertParagraphAfter; Rng.InsertAfter("4,5,6"); Rng.InsertParagraphAfter; Rng.InsertAfter("7,8,9"); Rng.InsertParagraphAfter; Rng.ConvertToTable(",");

Отметим, что внешний вид таблицы можно изменить с помощью свойства Format, а также с помощью свойств коллекции Columns, представляющей колонки таблицы, и коллекции Rows, представляющей строки таблицы объекта Table.

Обращение к свойствам документа

Свойства документа можно получить с помощью коллекции BuiltInDocumentProperties объекта Document, например:

Memo1.Lines.Add("Название - " + _ App.ActiveDocument.BuiltInDocumentProperties .Value); Memo1.Lines.Add("Автор - " + _ App.ActiveDocument.BuiltInDocumentProperties . _ Value); Memo1.Lines.Add("Шаблон - " + _ App.ActiveDocument.BuiltInDocumentProperties . _Value)

Константы, необходимые для обращения к свойствам документа по имени, приведены в листинге 3 .

Итак, в данном разделе мы изучили основные операции, которые наиболее часто применяются при автоматизации Microsoft Word. Естественно, возможности автоматизации Word далеко не исчерпываются приведенными примерами, однако я надеюсь, что, руководствуясь основными принципами создания контроллеров Word, изложенными в данной статье, и соответствующим справочным файлом, вы сможете ими воспользоваться - мы с вами уже убедились, что это вовсе не так сложно.

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

Все остальные объекты Excel являются так называемыми внутренними объектами.

Небольшой фрагмент объектной модели Microsoft Excel изображен на рис.2:

Основным в объектной модели Excel является объект Application, содержащий коллекцию Workbooks объектов типа WorkBook. Каждый объект типа WorkBook содержит коллекцию WorkSheets-объектов типа WorkSheet, Charts типа Chart и др. Манипуляция рабочими книгами, их листами, ячейками, диаграммами реально осуществляется путем обращения к свойствам и методам этих объектов.

Ниже мы рассмотрим наиболее часто встречающиеся задачи, связанные с автоматизацией Microsoft Excel. Если вам встретилась задача, не совпадающая ни с одной из перечисленных, вы можете попытаться найти подходящий пример на Visual Basic в справочном файле VBAXL9.CHM, либо, как и в случае Microsoft Word, записать соответствующий макрос и проанализировать его код.

Запуск Microsoft Excel, создание и открытие рабочих книг

Для создания примеров использования Microsoft Excel можно использовать код создания контроллера, приведенный в разделе <Общие принципы создания контроллеров автоматизации>, заменив первый оператор в приведенном примере на следующий:

AppProgID:= "Excel.Application";

и заменить комментарии кодом, манипулирующим свойствами и методами объекта Excel.Application. Отметим, однако, что подключение контроллера автоматизации к имеющейся версии Excel с помощью метода GetActiveOleObject может привести к тому, что вся клиентская часть Excel окажется невидимой (это происходит, если имеющаяся копия Excel запущена в режиме, когда ее пользовательский интерфейс недоступен). Причины подобного поведения автору неизвестны. Поэтому, если существует вероятность возникновения подобной ситуации, лучше упростить код создания контроллера и всегда создавать новую копию Excel.

Изучение создания контроллеров Excel мы начнем с создания и открытия рабочих книг.

Создать новую рабочую книгу Excel можно, используя метод Add коллекции Workbooks объекта Application:

App.WorkBooks.Add;

Для создания рабочей книги на основе шаблона следует указать его имя в качестве первого параметра метода Add:

App.WorkBooks.Add("C:\Program Files\Microsoft _ Office\Templates\1033\invoice.xlt");

В качестве первого параметра этого метода можно также использовать следующие константы:

Const xlWBATChart = $FFFFEFF3; //рабочая книга состоит из листа с диаграммой xlWBATWorksheet = $FFFFEFB9; //рабочая книга состоит из листа с данными

В этом случае рабочая книга будет содержать один лист типа, заданного указанной константой (график, обычный лист с данными и др.)

Для открытия уже существующего документа следует воспользоваться методом Open коллекции WorkBooks:

App.Documents.Open("C:\MyExcelFile.xls");

Отметим, что свойство ActiveWorkBook объекта Excel.Application указывает на текущую активную рабочую книгу среди одной или нескольких открытых. Помимо этого к рабочей книге можно обращаться по ее порядковому номеру, например ко второй открытой рабочей книге можно обратиться так:

App.WorkBooks

Обратите внимание на то, что в Delphi при использовании позднего связывания синтаксис, используемый для обращения к членам коллекций объектов Excel, отличен от синтаксиса, используемого для обращения к объектам Word - в случае Word мы использовали метод Item, а в случае Excel мы обращаемся к членам коллекции как к элементам массива. Если же вы используете Visual Basic, синтаксис, применяемый для обращения к членам коллекций, будет одинаков для всех коллекций Microsoft Office.

Сделать рабочую книгу активной можно с помощью метода Activate:

App.WorkBooks.Activate;

Следующее, чему следует научиться - это сохранять рабочие книги в файлах.

Сохранение, печать и закрытие рабочих книг Microsoft Excel

Закрытие документа может быть осуществлено с помощью метода Close:

App.WorkBooks.Close;

App.ActiveWorkBook.Close;

Метод Close имеет несколько необязательных (в случае позднего связывания) параметров, влияющих на правила сохранения рабочей книги. Первый из параметров принимает значения True или False и влияет на то, сохранять ли изменения, внесенные в рабочую книгу. Второй параметр (типа Variant) - имя файла, в котором нужно сохранить рабочую книгу (если в нее были внесены изменения). Третий параметр, также принимающий значения True или False, влияет на то, следует ли пересылать документ следующему пользователю по электронной почте, и может быть проигнорирован, если эта функциональность не используется.

App.ActiveWorkBook.Close(True,"C:\MyWorkBook.xls");

App.ActiveWorkBook.Save;

App.ActiveWorkBook.SaveAs("C:\MyWorkBook.xls");

Метод SaveAs имеет более десятка параметров, влияющих на то, как именно сохраняется документ (под каким именем, с паролем или без него, какова кодовая страница для содержащегося в ней текста и др.).

Закрыть сам Excel можно с помощью метода Quit объекта Excel.Application. В случае Excel этот метод параметров не имеет.

Вывод документа Excel на устройство печати можно осуществить с помощью метода PrintOut объекта WorkBook, например:

App.ActiveWorkBook.PrintOut;

Если нужно изменить параметры печати, следует указать значения соответствующих параметров метода PrintOut (в случае Excel их восемь).

Обращение к листам и ячейкам

Обращение к листам рабочей книги производится с помощью коллекции WorkSheets объекта WorkBook. Каждый член этой коллекции представляет собой объект WorkSheet. К члену этой коллекции можно обратиться по его порядковому номеру, например:

App.WorkBooks.WorkSheets.Name:= _ "Страница 1";

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

К листу рабочей книги можно обратиться и по имени, например:

App.WorkBooks.WorkSheets["Sheet1"].Name:= _ "Страница 1";

Обращение к отдельным ячейкам листа производится с помощью коллекции Cells объекта WorkSheet. Например, добавить данные в ячейку B1 можно следующим образом:

App.WorkBooks.WorkSheets["Sheet1"].Cells.Value:="25";

Здесь первая из координат ячейки указывает на номер строки, вторая - на номер столбца.

Добавление формул в ячейки производится аналогичным способом:

App.WorkBooks.WorkSheets["Sheet1"] _ .Cells.Value:= " =SUM(B1:B2)";

Очистить ячейку можно с помощью метода ClearContents.

Форматирование текста в ячейках производится с помощью свойств Font и Interior объекта Cell и их подсвойств. Например, следующий фрагмент кода выводит текст в ячейке красным жирным шрифтом Courier кегля 16 на желтом фоне:

App.WorkBooks.WorkSheets.Cells.Interior _ .Color:= clYellow; App.WorkBooks.WorkSheets.Cells.Font _ .Color:= clRed; App.WorkBooks.WorkSheets.Cells _ .Font.Name:= "Courier"; App.WorkBooks.WorkSheets.Cells _ .Font.Size:= 16; App.WorkBooks.WorkSheets.Cells _ .Font.Bold:= True;

	Программный идентификатор