Автоматизация заполнения документов, часть 4: Перенос данных в Word - Excel для экономиста

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

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

Итак, обо всем по порядку.

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

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

Расположение кнопки "Вставить закладку"

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

Я решил для удобства поместить все данные, которые необходимо перенести в документ 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. Один из них - шаблон, содержащий программу заполнения закладок, а второй - форма для заполнения. Вы можете использовать без изменений шаблон с программой, но форму для заполнения, естественно, придется переделать в соответствии с Вашими нуждами.

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

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

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

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

Алексей, механизм примитивный - нужные значения просто копируются в нужные ячейки. Посмотрите в коде примера, в файле Excel это процедура TransferToDatabase. Если все-таки нужны пояснения, давайте я напишу отдельной статьей или письмом по электронной почте.

Дмитрий 28.10.2013 at 13:59 · Reply →

Статья действительно очень полезная! И еще более интересная!
Но насколько бы механизм не был бы примитивным, совсем непонятны некоторые его моменты. А именно TransferToDatabase. Да, пояснения действительно нужны) Если у Вас найдется время и возможность, то я, и уверен много кто еще, были бы крайне благодарны за разъяснения!

Дмитрий 30.10.2013 at 10:13 · Reply →

Добрый день!
Ожидая Вашего ответа, разбираюсь в столь чудесном решении и стараюсь не терять время зря. Оптимизирую Ваше творчество под свои нужды и столкнулся с тем, что нужна возможность загрузки шаблона в зависимости от значения в ячейке. По принципу как выбирается адрес сохранения файла. Подскажите, пожалуйста, как это реализовать?
Правильно ли я понимаю, что надо:
1. убрать Const FILL_MACROS_DOC As String = "TemplateFillDocument.dotm"
2. добавить Dim FILL_MACROS_DOC As String
3. добавить
Select Case ThisWorkbook.Names("Имя моей ячейки").RefersToRange.Value
Case "Мой вариант №1":
newFileName = "Шаблон №1"
Case "Мой вариант №2":
newFileName = "Шаблон №2"
Case "Мой вариант №3":
newFileName = "Шаблон №3"
End Select
В какую часть макроса надо поставить данный "кусок"?

Дмитрий, я бы разместил весь блок Select Case в отдельной функции:

А в том месте, где раньше использовалась константа FILL_MACROS_DOC, вызвал бы эту функцию. Как-то так.

Дмитрий 01.11.2013 at 06:47 · Reply →

То что нужно! Спасибо!!))
А можно, пожалуйста, небольшое пояснение по SortByCode? С ТрансфертудатаБейз вроде бы понятно, а вот с SortByCode неясно вообще ничего. Никак не получается ее адаптировать вот свой документ.

Евгений Терехов 31.01.2014 at 07:32 · Reply →

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

Владимир 30.01.2014 at 18:33 · Reply →

Евгений,
подскажите не распознается переменная rngBookmarksList в строке:
vaBookmarks = wksBookmarks.Range("rngBookmarksList").Value

Где она объявлена не могу найти.

Евгений Терехов 31.01.2014 at 07:17 · Reply →

Владимир, добрый день.
rngBookmarksList это не переменная, а имя диапазона. Задается вот такой формулой:

Лист Bookmarks - скрытый.

Владимир 20.02.2014 at 13:29 · Reply →

Евгений, спасибо.
еще вопрос: где этот диапазон задается?

Евгений Терехов 20.02.2014 at 15:25 · Reply →

Владимир, добрый день.
Это делается через "Диспетчер имен" - вот в этой статье. в разделе "Как присвоить имя константе или формуле" описан процесс.

Михаил 28.02.2014 at 18:14 · Reply →

Расскажите пожалуйста подробнее про формулу в ячейке D7 на листе Input.

Евгений Терехов 03.03.2014 at 10:24 · Reply →

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

Василий 12.03.2014 at 18:48 · Reply →

Возможно ли запустить аналогичную систему для 2002 офиса, и, если да, много ли придется изменить?

Евгений Терехов 15.03.2014 at 20:41 · Reply →

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

Максим 07.05.2014 at 09:21 · Reply →

А не подскажете, что требуется изменить в макросе для запуска в 2010 офисе?

А то опытным путем было выяснено, что запарывается на строке

'Запускаем код заполнения закладок в Word, передаем всю необходимую информацию
wrdApp.Run FILL_MACROS_SUB, newFilePath, newFileName, vaBookmarks

с ошибкой "Run-time error '6294': Тип и расширение файла несовместимы."

Если что, в коде вообще ничего не менял, просто скачал архив и ни разу корректно так и не запустил:(

Евгений Терехов 11.05.2014 at 17:05 · Reply →

Максим, добрый день.
Эти макросы разрабатывались в Офисе 2010, так что должны работать без изменений. Вы используете 32- или 64-битную версию Офиса?
На форумах пишут, что такие ошибки могут возникать либо в 64-битных версиях, либо в случаях, когда в системе отсутствуют какие-то dll-файлы (что мне кажется скорее рекламой утилит чистки и восстановления реестра, чем реальной проблемой).

Виктория 12.09.2014 at 14:32 · Reply →

Уважаемый Евгений.
А если мне требуется автоматический перенос таблицы из ворда, но ежемесячно с изменением диапазона данных? Поточнее: есть обычный управленческий отчет по месяцам, к которому ежемесячно пишутся комментарии в ворде с различными частями этого отчета. Можно как-то это автоматизировать, чтобы не переносить ежемесячно №ное количество данных из таблиц? Строки таблиц фиксированные.

Игорь 21.10.2014 at 01:18 · Reply →

Здравствуйте, Евгений!
Подскажите пожалуйста, как осуществить перенос наших данных из Excel с сохранением формата (количество знаков после запятой, цвет и так далее).

Евгений Терехов 21.10.2014 at 20:10 · Reply →

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

Игорь 22.10.2014 at 00:33 · Reply →

Спасибо, Евгений! Буду пробовать.

Анна 29.10.2014 at 15:15 · Reply →

Добрый день, Евгений!
Большое Вам спасибо за эту статью. Я сделала на ее основе свою базу данных с подгрузкой анкеты из другого файла и выгрузкой форм в ворд. Но столкнулась с проблемой. Иногда необходимо одновременно загружать несколько строк данных из базы и переносить их в одну таблицу Ворда, проблема в том, что неизвестно сколько будет строк и я не могу заранее прописать для них всех закладки как в шаблоне, так и в книге. Можете посоветовать какой-нибудь подходящий моей задаче код? Сама придумала только очень сложный вариант. Сделать цикл на количество строк и просто повторять все процедуры нужное количество раз. Сначала обработка первой строки и вставка закладок, потом второй и т.д. Но для этого надо добавить в ворд код удаления закладок из одной строки, добавление новой строки и присвоение ячейкам новой строки имен удаленных закладок.

Александр 31.10.2014 at 16:19 · Reply →

ДОбрый день, Евгений.
Очень обрадовался, когда нашел "вменяемый способ" передать содержимое из Excell в Word. Но! Оказалось, что все не так просто как написано у вас в статье.
Перечислю по порядку с чем я столкнулся.
1. Если в дате вместо разделительной точки подсунуть запятую, или др знак, то кажется, что EXCELL зависает. На самом деле в ворде выдается диалог, а он из-за других окон не виден. Т.е. некорректно работает тест на ошибки ввода
2. Если Ворд настроен сохранять в формате 2003-2007, то макрос не выполняется.
Но это я как бы нашел и поправил у себя. Стало вроде работать, но работает только заполнение базы. Вордовский файл тоже формируется, но при попытке его открыть пишет, что ворд открыть его не может из-за проблем с содержимым файла.
Тестил на ваших файлах и ничего в них не менял.
Чтобы это могло быть?
А вообще ваши статьи полезные. Спасибо.

Иван 03.11.2014 at 16:10 · Reply →

Евгений, добрый день! Нужно реализовать подобную задачу, однако там все будет немного проще. С Вами как то можно связаться на этот счёт?

Лев 24.03.2015 at 17:17 · Reply →

Евгений, добрый день! Хотел реализовать Ваш способ на своем документе. Но столкнулся с проблемой: Закладка формируется только в одном месте. А у меня номер документа например встречается в нескольких местах в шаблоне.
Можете что-нибудь посоветовать?

Евгений Терехов 26.03.2015 at 08:11 · Reply →

Лев, добрый день.
Да, механизм с закладками далек от совершенства :) Тем не менее, количество закладок может быть сколь угодно большим, поэтому Вы можете создать несколько закладок с разными именами на листе Bookmarks в рабочей книге Excel, и всем им присвоить одно и то же значение.

Sasha 29.04.2015 at 22:02 · Reply →

Привет! А на MAC это работает?
У меня постоянно выдаёт ошибку.

Для переноса данных из excel в word можно воспользоваться этой программой http://softpro.su/download/Autozapolnenie_shablonov.zip

Илья 21.07.2016 at 14:15 · Reply →

Добрый день! Вариант замечательный, что бы, кто не писал. Спасибо огромное автору. Только есть маленький вопрос. При необходимости добавить отдельно таблицу необходима доработка. Я нашёл вот такой вариант для отдельной кнопки может кто подскажет куда и в каком виде его прикрутит?
Sub TextBox1_Click()
Err.Clear
Application.ScreenUpdating = False
'On Error Resume Next
Set WordApp = CreateObject("word.application")

With WordApp
.Visible = True
.ScreenUpdating = True
.DisplayAlerts = False

Documents.Open (ThisWorkbook.Path & "\dot\2KZ.dot")
End With

Set objDoc = WordApp.ActiveDocument
Sheets("Sostav").Range("N1:Q" & Sheets("Sostav").Range("B65536").End(xlUp).Row).Copy
With objDoc
.Bookmarks("Tab_1").Range.PasteExcelTable False, False, False ' В документе прописан Bookmark
.Tables(2).AutoFitBehavior 2
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
If Err > 0 Then MsgBox "Во время выполнения макроса были ошибки!": Exit Sub
On Error GoTo 0
MsgBox "Макрос завершил свою работу!"
End Sub

Свежие записи Свежие комментарии Облако меток Категории статей

Онлайн-помощь по Excel

Создание форм для заполнения и печати в Excel - Excel for Mac

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

Выполните одно из перечисленных ниже действий.

Действие 1. Отображение вкладки "Разработчик"

В правой части ленты нажмите кнопку и выберите пункт Параметры ленты .

В разделе Настройка установите флажок Разработчик .

Действие 2. Добавление элементов управления содержимым и настройка формата

На вкладке Разработчик в группе Элементы управления формы выберите элемент, который хотите добавить.

Щелкните на листе место, в которое требуется вставить элемент.

Чтобы задать для элемента свойства, нажмите клавишу CONTROL. удерживая ее, щелкните элемент и выберите пункт Формат элемента управления.

В поле Формат элемента управления задайте свойства, такие как шрифт, выравнивание и цвет.

Повторите действия с 1-го по 4-е для каждого элемента, который хотите добавить.

Действие 3. Защита листа с формой

В меню Сервис наведите указатель мыши на пункт Защита и выберите команду Защитить лист.

Выберите параметры защиты.

Сохраните и закройте книгу.

Совет : Чтобы продолжить редактирование после защиты формы, в меню Сервис наведите указатель мыши на пункт Защитить лист и выберите команду Снять защиту листа .

Действие 4. Проверка формы (необязательно)

Если необходимо, перед распространением форму можно проверить.

Защитите форму, как описано в действии 4.

Снова откройте форму, заполните ее, как это сделал бы пользователь, и сохраните копию.

Примечание : Чтобы сэкономить время, можно создать форму на основе шаблона. Шаблоны можно найти на странице шаблонов Office for Mac.