Макрос для заполнения файла word данными из excel

Обновлено: 22.01.2025

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

Немного поясню задачу, допустим, нам необходимо сформировать какие-то специфические документы по шаблону массово, т.е. в итоге их получится очень много, как я уже сказал выше, например, по каждому сотруднику. И это нужно сделать непосредственно в Excel, если было бы можно это сделать в Word, то мы бы это сделали через «Слияние», но нам нужно именно в Excel, поэтому для этой задачи мы будем писать макрос.

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

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Пишем макрос на VBA Excel по формированию документов

Реализовывать нашу задачу будем на примере «Электронной карточке сотрудника» (я это просто придумал:), хотя может такие и на самом деле есть), т.е. документ в котором хранится личные данные сотрудника вашего предприятия, в определенном виде, именно в Excel.

Примечание! Программировать будем в Excel 2010.

И для начала приведем исходные данные, т.е. сами данные и шаблон

Данные.

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон.

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

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

Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»

Свои поля я назвал следующим образом:

Код макроса на VBA Excel

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

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

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

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

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

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

При нажатии кнопки запуска макрос на основе шаблона dot создаёт очередной файл, и в этом документе производит замену текста ("кода поля") из первой строки файла Excel на значение поля (из очередной строки с данными файла Excel)

Папка для сформированных документов создаётся автоматически, и содержит в имени текущую дату и время
(например, созданная папка будет называться Договоры, сформированные 01-05-2011 в 15-03-24)

Имена создаваемых файлов формируются объединением полей фамилия, имя и отчество, с добавлением расширения doc

PS: Макрос был написан достаточно давно, когда я только начинал изучать VBA, - так что код недостаточно универсален.

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

Ознакомьтесь также с универсальной надстройкой формирования документов по шаблонам,
которая может делать всё тоже самое, что и эта программа,
только в качестве шаблонов могут выступать, помимо документов Word, ещё текстовые файлы, и книги Excel.

В надстройке - много возможностей, и полезных дополнений: склонение ФИО в родительный и дательный падежи, автоматический вывод на печать (с заданным количеством копий), размещение созданных файлов в разных папках, создание и рассылка писем со вложениями, и множество других полезных функций.

По вышеприведённой ссылке программа заполнения документов Word из Excel доступна для бесплатного скачивания.

Внимание: просьбы о доработке макроса, описанного в этой статье, не принимаются.

Есть новая (универсальная) версия, - в которой уже есть практически всё, что может понадобиться.

Здравствуйте. Спасибо вам за этот макрос, который помог мне в работе.
Не могли бы вы еще подсказать как сделать чтоб в шаблон Word прописывались данные из нескольких (разных) строк Excel?
Заранее спасибо.

Подскажите. а можно сделать чтобы макрос обрабатывал только те строки в которых стоит номер договора, а если пусто пропускал?

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

Евгений, воспользуйтесь универсальной программой для заполнения документов Word - там реализована подстановка значений в колонтитулы

Вова, если нет денег на заказ, есть только 2 варианта:
1) разобраться в этих макросах, и сделать самому (благо, примеров подобных макросов в интернете предостаточно)
2) обратиться за помощью на любой из форумов по Excel (но, опять же, если с вашей стороны никаких наработок нет, вряд ли кто сделает всю работу за вас)

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

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

Подскажите пожалуйста, как сделать так, чтобы макрос формировал один документ ворд на основании данных из нескольких строк Excel?

А как реализовать те же возможности, только используя функцию слияния в Word. Чтобы каждый новый раз не формировался новый документ а запись делалась на новой странице?

Теперь доступна новая версия универсальной программы формирования документов по шаблонам.

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

Количество файлов-шаблонов (Word, Excel, TXT) неограничено.

Замечательно. Я, долгое время искал нечто подобное. Но к счасливой случайности вышел на Ваш сайт. Установил панели для VBA. И ещё коу-что. Практически разобрался в работе данного макроса. В VBA я, недавно. Но поставил перед собой задачу изучить его. Для этого я, решил составить программу для заполнения бланков аттестатов о среднем образовании и бланков дипломов об окончании ПТУ. До этого они заполнялись вручную. Задачу в Excel, я, программно решил, не безВашего в ней участия, некоторые фрагменты кода. У меня база заполняется посредством форм в виде развёрнутого аттестата. А, вот транспортировка данных в Word с листов - разворот и последняя страница. Как-то чуть-чуть не вяжется с общим видом программы. Нельзя-ли макрос транспортировки "засунуть" под форму? Если можно, то не смогли бы Вы посоветовать мне как это сделать. Делаю эту программу потому, что на Украине все мучаются с авторизованым заполнением бланков об окончании учебных заведений. А, так я бы мог бесплатно, это моё безусловное кредо, предоставить программу желающим. Заранее Вам благодарен. Помогая мне Вы помогаете и до того замордованым классным руководителям школ Украины.

АВТОМАТИЗАЦИЯ ЗАПОЛНЕНИЯ ДОКУМЕНТОВ, ЧАСТЬ 4: ПЕРЕНОС ДАННЫХ В WORD

В предыдущих статьях цикла "Автоматизация заполнения документов" я рассказал о том, как сформировать пользовательский интерфейс приложения, организовать проверку вводимых данных и получить число прописью без использования кода VBA. В этой, заключительной статье речь пойдет о волшебстве - переносе всех необходимых значений из рабочей книги Excel в документ Word. Давайте я покажу Вам то, что должно получиться в итоге:

Описание механизма

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

Создание шаблона документа Word

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

Для удобства поддержки напечатать в нужном месте название закладки. Я еще и заключил названия в угловые скобки, так что получилось, например, вот так: <DocumentDate> для закладки, содержащей дату документа.
Выделить все название закладки (в моем случае вместе с угловыми скобками), перейти на вкладку "Вставка" и нажать кнопку "Закладка":

Таким образом нужно будет создать все закладки, то есть отметить все места, куда будут вставлены данные из Excel. Получившийся файл нужно сохранить как "Шаблон MS Word" с помощью пункта меню "Файл" -> "Сохранить как. ".

Подготовка данных Excel

Я решил для удобства поместить все данные, которые необходимо перенести в документ Word, на отдельном рабочем листе с названием Bookmarks - закладки. На этом листе два столбца: в первом содержатся названия закладок (в точности так, как они названы в документе Word), а во втором - соответствующие значения, подлежащие переносу.

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

На этом этапе важно правильно указать все названия закладок - от этого зависит правильность переноса данных.

Процедура переноса

А вот это - самое интересное. Существует два варианта выполнения кода переноса данных:

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

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

Создать шаблон документа Word с поддержкой макросов. В этом шаблоне будет содержаться выполняемый код на VBA.

Почему нужно создать именно шаблон? Таким образом можно защитить программный код от неумышленного изменения. Дело в том, что каждый раз при открытии шаблона создается новый файл Word, сохранить который поверх существующего шаблона просто так не получится. Сам шаблон при этом остается неизменным.

Текст процедуры я приводить в статье не буду - его можно легко посмотреть в файле FillDocument.dotm , расположенном в папке Template в архиве с примером.

Как же воспользоваться всем этим для решения именно Вашей задачи?

Понимаю, что на словах это все выглядит очень просто, но что же получается на деле? Я предлагаю Вам просто воспользоваться уже готовым вариантом. Скачайте архив с примером, в рабочей книге Excel нажмите комбинацию клавиш Alt+F11, чтобы открыть редактор Visual Basic и прочитайте все мои комментарии к программе. Для того, чтобы изменить программу под свои нужды Вам понадобится всего лишь изменить значение нескольких констант, они вынесены в самое начало программы. Весь текст программы Вы можете свободно скопировать в свой проект.

Структура архива

В архиве, приложенном к этой статье, содержится несколько файлов.

Основной файл - рабочая книга Excel с названием "Создание подтверждений". В этой рабочей книге 4 рабочих листа, из которых отображаются только два: "Input" - лист ввода данных и "Database" - архив всех введенных документов.

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

Как переработать пример "под себя"?

Я рекомендую придерживаться следующей последовательности действий:

Подготовить шаблон документа Word, который необходимо заполнить. Создать в нем все необходимые закладки и сохранить как "шаблон MS Word".
Скопировать в папку с подготовленным шаблоном файл FillDocument.dotm из архива, приложенного к этой статье. Этот файл отвечает за заполнение закладок шаблона, и в нем ничего менять не нужно.
Подготовить рабочую книгу Excel для ввода данных. Вам решать, будет ли она обладать каким-либо "продвинутым" пользовательским интерфейсом и осуществлять разные хитрые расчеты. Главное, чтобы в ней содержался рабочий лист с таблицей соответствия имени закладки в шаблоне Word и значения, которое нужно подставить.
Вставить в подготовленную рабочую книгу код программы на VBA из файла-примера. Заменить все константы в соответствии с Вашим проектом.
Протестировать правильность работы.
Активно пользоваться!

Вместо заключения

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

Другие статьи из цикла:

По этой ссылке Вы перейдете на сайт Microsoft SkyDrive, где Вам будет предложено скачать файл Confirm.rar - это и есть архив приложения.

0 коммент.:

Архив блога

Ярлыки

Recent

1. Эстетика и юзабилити

— Таблицы это в первую очередь таблицы, безликие ячейки с подписанными колонками и строками. Однако очень часто мы сталкиваемся с ситуацией, когда необходимы дополнительные пояснения к значению, которое будет находится в ячейке, или требуется дополнительно активизировать внимание пользователя на важности вводимого значения. Особенно важно, если у Вас, как в моем случае, строки в колонке очень длинной таблицы содержат разноплановую информацию, например: даты, виды работ, материалы, подписанты и многие др. В таких случаях у нас есть 2 инструмента для решения задачи:

Есть и минусы такого решения, в частности всплывающие подсказки могут раздражать, но в ситуации, когда на объекте 15" мониторы на ноутбуках с разрешением 1366×768 это разумный компромисс, что бы рабочая область была как можно больше.

Если внимательно проанализировать данные, то окажется что в таблице будут ячейки 3х типов:

ячейки в которые непосредственно необходимо вводить новую текстовую информацию;
ячейки, значение которых может принимать значение из ограниченного диапазона, введенного заранее, например: ФИО и должность подписантов;
ячейки в которых прописаны формулы, например есть часть данных которая будет повторяться из акта в акт и такую информацию достаточно ввести один раз, например: наименование объекта, участок, организация и т.п.; либо формулы призванные реализовать технические возможности, например: переноса строки, подтягивание объемов работ, регалий по ФИО и т.п.

Здесь первая процедура постоянно будет защищать лист при помощи пароля 111, вторая будет блокировать функционал вырезать-вставить. Надо ли говорить, что это все работает только при включенных макросах, но с другой стороны без них и файл на 100% функционировать не будет.
Для случаев же п.2 разумно завести лист где столбцы будут содержать меняющиеся значения, прописать в них ссылки на диапазоны, присвоить им имена, т.е. на вкладке «Формулы» -> «Диспетчер имен» каждому диапазону присвоить имена и через вкладку «Данные» -> пункт меню «Проверка данных» -> вкладка «Параметры» -> условие проверки — «Список» реализовать выпадающее меню.

И, конечно, не забывайте ставить условия форматирования цветом, например для случаев, когда заполнены все необходимые строки в столбце через «Условное форматирование», например формула условного форматирования закрашивает ячейку, если следующие ячейки под ней содержат текст: =И(ДЛСТР(E5)>0; ДЛСТР(E6)>0)

2. Оптимизация кода + нововведения

Начать придется издалека, а именно вернуться к вопросу о реализации механизма заполнения шаблона. Если Вы решите заполнить шаблон в формате Excel и в формате Word, то это будут совершенно 2 разных механизма. В основе своей в файл Excel пишутся значения в конкретные ячейки файла или диапазоны ячеек и имеют привязку вида (у, х) (не спрашивайте почему у них строка идет впереди столбца при адресации — не знаю), например: Worksheet.Cells(y, x) = k. Отсюда же и первая мысль, что заполнять Excel-шаблон можно либо явным образом, т.е. непосредственно весь макрос будет содержать что откуда берется и куда закладывается, но что если придется вносить изменения в таблицы данных или выйдет новая форма шаблона? Отсюда вторая идея реализации, код которой описан в первой статье — это парсинг некоторых символов, которыми сперва заполняется массив, а так же в свою очередь содержит файл шаблона в нужных местах. Затем в каждой строке шаблона ищется совпадение с элементами массива поочередно, если совпадение есть, то порядковый номер массива привязан к строке таблицы откуда берутся данные, а столбец берется с листа в котором мы указываем какие именно акты мы хотим вывести. Итого несколько вложенных циклов, что накладывает ограничения на форматирование шаблона Excel, чем проще — тем лучше, потому что чем больше ячеек парсить — тем дольше будет происходить заполнение шаблона данными.

По многочисленным просьбам мною была интегрирована возможность вывода в шаблон формата Word, и здесь на самом деле есть 2 способа вывода текста:

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

Здесь вынесена в отдельную процедуру обращение к закладке и arrСсылкиДанных(i) — это массив который содержит управляющие символы. Издержки метода, если Вам потребуется сослаться на значение Закладки в другом месте, например дату нужно использовать в заголовке и напротив фамилии каждого подписанта, то необходимо использовать в шаблоне Меню «Вставка» -> пункт меню «Перекрестная ссылка» -> Тип ссылки: «Закладка», Вставить ссылку на: «Текст закладки» и снять галочку «Вставить как гиперссылку». Что бы это отобрадзилось корректно не забудте обновить в конце макроса перед выводом поля Wd.Fields.Update

2. Если рисовать таблицы средствами Word, то к ним можно обращаться с адресацией в ячейку
Здесь нужно обратить внимание, что у каждой таблицы в Word есть свой внутренний номер, методом нехитрого перебора Вы найдете нужный, а дальше принцип тот же, что и в Excel.

Между выводами в файлы форматов Word и Excel есть огромная пропасть, которая заключается в следующем:

Шаблон Excel требует перед использованием настроить отображение под конкретный принтер, т.к. фактическая область печати разнится от модели к модели. Так же перенос строки текста возможен, но только в пределах ячейки/объединенных ячеек. В последнем случае не будте автораздвигания строки, в случае переноса текста. Т.е. Вам вручную придется заранее определит границы области, которые будут содержать текст, который в свою очередь в них еще должен убраться. Зато Вы точно задали границы печати и выводимого текста и уверены, что не съедет информация (но не содержание) с одного листа на другой.

Шаблон Word при настройке автоматически переносит текст на последующую строку, если он не убрался по ширине ячейки/строки, однако этим самым он вызывает непрогнозируемый сдвиг текста по вертикали. Учитывая тот факт, что по требованиям к Исполнительной документации в строительстве ЗАПРЕЩЕНО один акт печатать на 2х и более листах, то это в свою очередь так же рождает проблемы.

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

Для первой строки:

<=ЕСЛИОШИБКА(ЕСЛИ($F$20<>"-"; ЕСЛИ(ДЛСТР('Данные для проекта'!$C$3)<106;'Данные для проекта'!$C$3; ПСТР('Данные для проекта'!$C$3;1;105-ПОИСКПОЗ(" *"; ПРАВСИМВ(ПСТР('Данные для проекта'!$C$3;1;105); СТРОКА($1:$10));))));"-")>

<=ЕСЛИОШИБКА(ЕСЛИ($F$20<>"-"; ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105-ПОИСКПОЗ(" *"; ПРАВСИМВ(ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105); СТРОКА($1:$10));)));"-")>

Здесь используется принцип массивов, т.е. вводится такой текст по Ctrl + Shift + Enter, а не обычному Enter. Сами формулы располагаются в ячейках F1 и F2. 'Данные для проекта'!$C$3 — ссылка на наименования объекта, длина текста которого более 105 символов. Перенос организуется в случае превышения длины текста в 105 символов.

Еще одним нововведением стал общий реестр, а так же контроль списания материалов по актам АОСР, но здесь ничего нового, просто парсинг соответствующих строк в свяске ИНДЕКС + ПОИСКПОЗ, которые расписаны во многих мануалах.

3. Структура и связи

Но мой пост так бы и остался рядовым постом с очередной игрой в изобретание велосипеда инструментами, которые рассчитаны на совершенно другое, если бы ни одно НО(!) Месячно-суточный график.

Идея о том, что можно именно на него много чего повесить, например заполнение Общего журнала работ в части Раздела 3 — наименование работ по датам, очередность и необходимость Актов освидетельствования скрытых работ и не только — завладела моими мыслями. Обычно в Excel закрашивают даты, в зависимости от диапазонов дат — начало и конец, но не на стройке. На стройке в календарном графике пишут объемы, а в зависимости от того с какой даты напротив наименования работ стоят объемы и по которую — получаются диапазоны дат отчетных периодов. На скриншоте серым помечены объемы попадающие в систематизированные отчетные периоды (1мес). Таким образом получается, что если:

Читайте также: