Автоматическое создание документов Word на основе табличных данных excel

‘ Dim WA As Word.Application, WD As Word.Document: Set WA = New Word.Application ‘ c подключением библиотеки Word Dim WA As Object, WD As Object: Set WA = CreateObject("Word.Application") ‘ без подключения библиотеки Word

For Each row In ActiveSheet.Rows("2:" Application.PathSeparator Dim row As Range, pi As New ProgressIndicator r = Cells(Rows.Count, "A").End(xlUp).row: rc = r — 2 If rc < 1 Then MsgBox "Строк для обработки не найдено", vbCritical: Exit Sub

pi.Show "Формирование договоров": pi.ShowPercents = True: s1 = 10: s2 = 90: p = s1: a = (s2 — s1) / rc pi.StartNewAction , s1, "Запуск приложения Microsoft Word"

For Each row In ActiveSheet.Rows("3:" " " " " ФИО Создание нового файла на основании шаблона", ФИО Set WD = WA.Documents.Add(ПутьШаблона): DoEvents

pi.StartNewAction p + a / 3, p + a * 2 / 3, "Замена данных . ", ФИО For i = 1 To КоличествоОбрабатываемыхСтолбцов FindText = Cells(1, i): ReplaceText = Trim$(.Cells(i))

‘ так почему-то заменяет не всё (не затрагивает таблицу) ‘WA.Selection.Find.Execute FindText, , , , , , , wdFindContinue, False, ReplaceText, True

pi.StartNewAction s2, , "Завершение работы приложения Microsoft Word", " ", " " WA.Quit False: pi.Hide msg = "Сформировано " " договоров. Все они находятся в папке" НоваяПапка MsgBox msg, vbInformation, "Готово" End Sub

Function NewFolderName() As String NewFolderName = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, "Договоры, сформированные " /", "-"), ".", "-"): End Function Function Get_Time() As String: Get_Time = Replace(TimeValue(Now), ":", "-"): End Function Function Get_Now() As String: Get_Now = Get_Date в " кода поля") из первой строки файла Excel на значение поля (из очередной строки с данными файла Excel)

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

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

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

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

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

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

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

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

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

Спасибо, за макрос, не могли бы подсказать что в нем изменить что бы обрабатывалась только та строка в которой выделенная ячейка?

Спасибо за макрос. Очень помог в работе.

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

Макрос выдает ошибку (Type mismatch (Error 13)) при обработке ячейки с формулой, подскажите пожалуйста как решить эту проблему?

Здравствуйте. Спасибо вам за этот макрос, который помог мне в работе. Не могли бы вы еще подсказать как сделать чтоб в шаблон 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 нужно создать закладки. Это делается следующим образом:

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

Таким образом нужно будет создать все закладки, то есть отметить все места, куда будут вставлены данные из 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

Макрос создания документов Word по данным таблицы Excel

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

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

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

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

Вложения:

CreateWordDocuments2.zip

139.71 КБ

209945 просмотров

Антон, 17 Сен 2013 — 06:12. #1

Гость, 10 Июл 2013 — 11:28. #2

Спасибо за макрос. Очень помог в работе.

Игорь (администратор сайта), 6 Июн 2013 — 21:01. #3

User, 6 Июн 2013 — 17:22. #4

Игорь (администратор сайта), 29 Май 2013 — 22:58. #5
User, 29 Май 2013 — 08:15. #6

Гость, 6 Дек 2012 — 22:39. #7

Eugeny, 6 Дек 2012 — 19:03. #8

Игорь (администратор сайта), 28 Ноя 2012 — 15:29. #9

Eugeny, 28 Ноя 2012 — 13:59. #10

Добрый день! Макрос очень помог в работе. Доделал его немного под свои нужды: вывод в разные шаблоны документов word при разных значениях в excel. Остался один вопрос — в сам документ word текст вставляется отлично, а вот в колнтитулы — нет 🙁 Подскажите, пожалуйста, как это исправить?

Игорь (администратор сайта), 24 Авг 2012 — 11:11. #11

Вова, 24 Авг 2012 — 10:29. #12

Игорь (администратор сайта), 23 Авг 2012 — 21:23. #13

Вова, 23 Авг 2012 — 20:13. #14

Михаил, 17 Май 2012 — 10:05. #15

Игорь (администратор сайта), 19 Апр 2012 — 09:09. #16

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

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

Бесплатно скачать и протестировать программу можно по этой ссылке: http://excelvba.ru/programmes/FillDocuments

Serg, 23 Мар 2012 — 13:41. #17

Замечательно. Я, долгое время искал нечто подобное. Но к счасливой случайности вышел на Ваш сайт. Установил панели для VBA. И ещё коу-что. Практически разобрался в работе данного макроса. В VBA я, недавно. Но поставил перед собой задачу изучить его.

Для этого я, решил составить программу для заполнения бланков аттестатов о среднем образовании и бланков дипломов об окончании ПТУ. До этого они заполнялись вручную. Задачу в Excel, я, программно решил, не безВашего в ней участия, некоторые фрагменты кода. У меня база заполняется посредством форм в виде развёрнутого аттестата.

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

А, так я бы мог бесплатно, это моё безусловное кредо, предоставить программу желающим. Заранее Вам благодарен. Помогая мне Вы помогаете и до того замордованым классным руководителям школ Украины.

Гость, 16 Мар 2012 — 15:21. #18

можно ли по каждой записи сформировать 2 документа word?

Автоматическое создание отчетов в Word из таблицы данных Excel

При создании отчетов в Word с использованием данных из таблиц Excel люди обычно выполняют две задачи:

Иов А : Введите данные в таблицу Excel для расчета.
Работа B : Откройте документы Word, чтобы обновить результаты расчетов из Excel (текст отчета обычно не сильно меняется).

фигура 1 : Excel, данные за предыдущий период использованы для отчетов за июль 2005 г.

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

Например, мы рассматриваем отчет о пассажирских перевозках 4 производителей автомобилей HTX, LTD, QD и CT. Каждый месяц вам нужно включать данные текущего месяца и предыдущего месяца, чтобы сравнивать увеличение и уменьшение. Сейчас нам нужно провести анализ данных в августе 2005 года.

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

фигура 2 : Excel, данные за текущий период использованы для отчетов за август 2005 г.

Шаг 2: В H.3 проанализируйте данные, полученные в H.1. Что необходимо сделать, так это скорректировать данные предыдущего периода в данных этого периода в этой таблице анализа (т. Е. Изменить H.3 на H.4). Теперь мы свяжем данные из Excel в Word, чтобы следующие периоды нужно было исправить только в Excel, а затем сообщим Word автоматически обновлять данные из Excel.

Сначала перейдите в электронную таблицу Excel, скопируйте поле данных, необходимое как C2 в H.2 (поле выделено в H.2), а затем перейдите в Word и поместите курсор в то место, где вам нужно исправить данные (непосредственно перед число 272601 в H.3). Удалите значение 272 601 в H.3, затем нажмите на Правка> Специальная вставка меню, Специальная паста появится панель. Вы выбираете Вставить ссылку> Неформатированный текст а потом ОК (эти операции временно называются «Вставить ссылку»), новая часть вставляется как 273,780, как в H.4, но фон этого числа 273,780 серый. Проделайте то же самое с другими рисунками, которые нужно изменить. (Обратите внимание, что при копировании из Excel вы должны скопировать всю ячейку, а не просто ввести номер и скопировать).

Рисунок 3 : Word, анализируя данные H.1

Обратите внимание: если область Вставить ссылку не серая, выполните следующие действия: перейдите к Инструменты> Параметры меню выберите Вид вкладка Затенение поля Выбрать Всегда тогда ОК .

В частности, для цифр, которые могут иметь отрицательное или положительное значение, и это значение влияет на текст в документе Word, это немного сложнее. Что касается снижения на 44,44% в H.3: предыдущий месяц уменьшился на 44,44%, потому что значение в Excel было -44,44 соответственно (в H.1). В этом месяце значение в таблице Excel составляет 16,39 (в H.2), мы должны отображать текст как увеличение на 16,39% в Word. Как сделать следующее: сначала вы открываете электронную таблицу Excel (H.2), для тех столбцов, которые показывают уменьшение или уменьшение (в этом примере столбцы D и E), мы должны создать 2 дополнительных столбца, чтобы отображать текст для каждого столбец. Делаем столбец D следующим образом (столбец E делает то же самое):

re> Рисунок 4 : Word, анализ данных H.2 - Вставьте 2 столбца F и G, чтобы дополнить столбец D как H.5. В котором столбец F представляет знак столбца D, а столбец G представляет абсолютное значение столбца D.

Введите формулу F2 в поле:

= ЕСЛИ (D2 = 0; «не увеличивается и не уменьшается»; ЕСЛИ (D2> 0; «увеличить»; «уменьшить»))

Эта функция учитывает значение ячейки D2, если = 0, отображает «нет увеличения или уменьшения», если> 0, отображает «увеличение», если Скопируйте приведенную выше формулу для ячеек F3, F4, F5, F6.

Введите в ячейку G2 формулу:

= ЕСЛИ (D2 0; ABS (D2); “”)

Эта функция принимает абсолютное значение ячейки D2.

Скопируйте приведенную выше формулу для ячеек G3, G4, G5, G6. Вы получите те же сведения, что и H.5.

Теперь вернитесь к Word в H.3:

re> - Удалите слово «уменьшенный», в Excel скопируйте поле F5 в H.5, затем вставьте ссылку в Word.

Удалите «44,44», через Excel скопируйте ячейку G5 в H.5, а затем вставьте ссылку в Word.

Сделайте то же самое для других частей, которые требуют модификации.

Если все сделано правильно, вы получите отчет за август 2005 г., как H.4.

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

Обратите внимание: чтобы Word не обновлял данные автоматически при открытии файла: перейдите к Инструменты> Параметры> Общее меню снимите флажок ” Обновить автоматические ссылки при открытии “. В противном случае каждый раз, когда вы открываете файл Word со связанными данными, Word всегда запрашивает обновления.

Как автоматически создавать документы Word из табличных данных Excel