Сейчас мы с Вами рассмотрим пример реализации того, как можно выполнить слияние данных Word с данными Microsoft SQL Server, при этом данный процесс будет автоматизирован средствами VBA Access 2003 .

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

Однажды у меня встала задача автоматизировать слияние некого шаблона Word с данными расположенными на SQL сервере, при этом все это необходимо было внедрить в программу, разработанную в Access 2003 (ADP проект). И сегодня я покажу пример решения данной задачи.

Исходные данные

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

Итак, в качестве клиента, как я уже сказал, у нас будет выступать ADP проект Access 2003. В качестве источника данных для примера будет выступать SQL Server 2012 Express . На компьютере установлен Microsoft Office 2013 (и Access 2003 ).

Создадим на сервере тестовую таблицу и заполним ее данными (допустим в базе данных Test ). Для этого Вы можете запустить следующую SQL инструкцию .

CREATE TABLE dbo.TestTable(ID INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(50) NOT NULL, Price MONEY NULL, CONSTRAINT PRIMARY KEY CLUSTERED (ID ASC)) GO INSERT INTO dbo.TestTable(ProductName, Price) VALUES ("Компьютер", 500) GO INSERT INTO dbo.TestTable(ProductName, Price) VALUES ("Монитор", 400) GO INSERT INTO dbo.TestTable(ProductName, Price) VALUES ("Телефон", 200) GO INSERT INTO dbo.TestTable(ProductName, Price) VALUES ("Планшет", 300) GO INSERT INTO dbo.TestTable(ProductName, Price) VALUES ("Принтер", 250) GO SELECT * FROM TestTable

Создаем файл подключения к источнику данных MS SQL Server

Теперь давайте создадим файл подключения (ODC) к нашему источнику данных. Данный файл будет выступать своего рода «шаблоном файла подключения », так как впоследствии мы можем, и будем переопределять и базу данных и сам SQL запрос .

Для создания файла подключения к SQL серверу давайте откроем Word и стандартным способом создадим данный файл, т.е. с помощью функционала «Рассылки ». (Кстати пример создания подключения к SQL серверу из Excel мы с Вами уже рассматривали в материале – Excel — Подключение и получение данных с SQL сервера ).

Затем в окне выбора источника данных нажимаем кнопку «Создать ».

Потом вводим адрес сервера и нажимаем «Далее ».

Затем выбираем базу данных и таблицу для подключения, еще раз напомню, это всего лишь шаблон, все эти параметры мы будем переопределять, жмем «Далее ».

И в заключение вводим понятное название файла подключения, а также мы можем сразу его сохранить в нужный нам каталог путем кнопки «Обзор », по умолчанию он сохраняется в « C:\Users\Имя_Пользователя\Documents\Мои источники данных». Нажимаем «Готово ».

Все, файл создан, Word можем закрыть без сохранения.

Создаем шаблон Word для слияния

Теперь давайте подготовим шаблон Word, т.е. это тот документ, в который мы будем подставлять данные из базы данных SQL сервера.

Вся подготовка сводится к тому, что нам необходимо вставить поля слияния там, где это нам нужно. Это делается следующим образом. Вкладка «Вставка -> Экспресс-блоки -> Поле ».

Ищем поле MERGEFIELD и вводим название поля, которое будет соответствовать полю в источнике данных (в моем случае это ProductName и Price ). Жмем «ОК ».

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

Код VBA Access 2003 для слияния документа Word с источником данных MS SQL Server

Осталось написать код VBA, который будет осуществлять слияние. Для примера давайте добавим на форму кнопку StartMerge и поле Price для фильтрации данных. Затем в редакторе Visual Basic напишем процедуру для слияния, допустим с названием MergeWord, и в обработчик события кнопки StartMerge (нажатие кнопки ) вставляем код вызова этой процедуры. Весь код будет выглядеть следующим образом (я его прокомментировал ). Сразу поясню, что шаблон Word и файл ODC у меня лежат в каталоге D:\Test\.

"Процедура для запуска слияния Private Sub MergeWord(TemplateWord As String, QuerySQL As String) "Первый параметр - Путь к шаблону Word "Второй параметр - Строка запроса к БД On Error GoTo Err1 Dim ConnectString As String, PathOdc As String Dim WordApp As Object Dim WordDoc As Object "Шаблон файла ODC для подключения к данным PathOdc = "D:\Test\TestSourceData.odc" If TemplateWord <> "" Then "Создаем документ Word Set WordDoc = CreateObject("Word.document") Set WordDoc = GetObject(TemplateWord) Set WordApp = WordDoc.Parent "Создаём подключение к источнику данных (MS SQL Server) "Некоторые данные берём из текущего подключения ADP проекта ConnectString="Provider=SQLOLEDB.1; " & _ "Integrated Security=SSPI;" & _ "Persist Security Info=True; " & _ "Initial Catalog=" & CurrentProject.Connection.Properties("Initial Catalog") & "; " & _ "Data Source=" & CurrentProject.Connection.Properties("Data Source") & "; " & _ "Use Procedure for Prepare=1;" & _ "Auto Translate=True;" & _ "Packet Size=4096;" & _ "Use Encryption for Data=False;" "Задаем источник данных WordDoc.MailMerge.OpenDataSource NAME:=PathOdc, _ Connection:=ConnectString, _ SQLStatement:=QuerySQL "Делаем видимым Word WordApp.Visible = True WordApp.Activate "Начинаем слияние With WordDoc.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute Pause:=False End With "Закрываем шаблон без сохранения WordDoc.close (wddonotsavechanges) Set WordDoc = Nothing Set WordApp = Nothing Else MsgBox "Не указан шаблон для слияния", vbCritical, "Ошибка" End If Ex1: Exit Sub Err1: MsgBox Err.Description WordDoc.close (wddonotsavechanges) WordApp.Quit Set WordDoc = Nothing Set WordApp = Nothing Resume Ex1 End Sub Private Sub StartMerge_Click() Dim Filter As String Filter = "" "Условие If Nz(Me.Price, "") <> "" Then Filter = "WHERE Price >= " & Me.Price End If "Вызов процедуры слияния Call MergeWord("D:\Test\Шаблон.docx", "SELECT * FROM ""TestTable"" " & Filter & " ") End Sub

Сохраняем и проверяем работу.

После нажатия на кнопку (StartMerge) запустится Word, в котором уже все данные заполнены и документов будет столько, сколько строк в источнике.

Как видим, все работает. На этом у меня все, надеюсь, материал был полезен. Пока!

Недавно в одной организации наблюдала дикую картину:)

Компания переезжала в новое здание - из МО в Москву. Речь, соответственно, об изменении существенных условий трудового договора для всех сотрудников. По процедуре сотрудников нужно уведомить под роспись о предстоящем переезде, а затем заключить со всеми дополнительные соглашения к трудовым договорам. В организации работают около 1,5 тысяч человек, отдел кадров должен срочно подготовить документы.

Что же происходит дальше?
Начальник отдела кадров направляет запрос в отдел информационных технологий о доработке кадровой системы: нужно создать печатные формы - уведомление и дополнительное соглашение. ИТ отвечают, что подобная доработка будет стоить столько-то тысяч евро, а расходы в бюджет не заложены, и, кроме прочего, реализация возможна не ранее, чем через два месяца.

Посылая проклятия в адрес отдела ИТ, кадровики выходят на работу в выходные, бросив дома маленьких детей. Бессмертный трудовой подвиг: за двое суток создано 3000 документов! В шаблон вручную добавляли имя и паспортные данные каждого работника, и так 3000 раз!

Смогут ли они простить себе так бездарно потраченное время, когда узнают, что используя Слияние (Mail Merge) шаблона в Word с таблицей Excel, содержащей персональные данные, сделать это можно было минут за двадцать:)

ШАГ 1. Создаем шаблон документа в Word.

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

ШАГ 2. Создаем источник данных в Excel.

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

ШАГ 3. Возвращаемся к шаблону документа, который мы создали в Word. Нам предстоит работать со вкладкой "Рассылки", внимательно изучите ее.

Нажимаем кнопку "Выбрать получателей", выбираем вариант "Использовать существующий список" и открываем источник данных (файл Excel с информацией о сотрудниках).

В следующем окне выбираем лист файла, на котором содержатся нужные данные.

ШАГ 4. Ставим курсор в том месте текста шаблона, куда нужно вставить данные из источника, нажимаем кнопку "Вставить поле слияния" и выбираем нужное поле.

Вот так выглядит мой шаблон после того, как вставлены все поля слияния:

ШАГ 5. Создаем Правила, изменяющие окончания слов "уважаемый" и "получил" в зависимости от пола сотрудника.

Ставим курсор после перед окончанием слова. Нажимаем кнопку "Правила" и выбираем "IF...THEN...ELSE" в выпадающем списке.

Записываем правило: если пол женский - тогда..., иначе...

ШАГ 6. Форматируем даты.

Если раньше вам уже приходилось использовать Рассылки (Слияние), вы наверняка сталкивались с тем, что даты, перенесенные из Excel, выглядят в Word совершенно не так, как нужно. Например, вместо 19.06.2012 вы, скорее всего, увидите 6/19/2012, вряд ли такой результат вас устроит.

Есть разные способы решить эту проблему, воспользуемся самым простым и удобным.

Нажмем сочетание клавиш Alt+F9, теперь мы можем видеть коды полей слияния.

Внутри кода поля Дата_договора перед закрывающей фигурной скобкой поставим обратную косую черту, а после нее ключ форматирования:
{ MERGEFIELD "Дата_договора" \@ "DD.MM.YYYY" }
@ - обозначение даты, "DD.MM.YYYY" - ключ формата даты вида 19.06.2012 .
Вы можете использовать любой другой формат. Например, чтобы дата имела вид 19 июня 2012 г. , используйте ключ \@ "DD MMMM YYYY г." .

Чтобы выйти из режима редактирования кодов, снова нажимаем Alt+F9.

ШАГ 7. Нажимаем кнопку "Просмотр результатов", чтобы проверить, что получилось.

ШАГ 8. Редактируем список сотрудников.

Нажав на кнопку "Изменить список получателей" можно исключить отдельные записи из списка, проверить, нет ли повторов, установить сортировку или фильтры.

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

ШАГ 8. Завершаем слияние.

Нажимаем кнопку "Найти и объединить". Если выбрать вариант "Печать документов", документы будут сразу отправлены на принтер. Если же нужно перед печатью просмотреть документы и внести правки, выбираем "Изменить отдельные документы".

Будет сформирован отдельный файл Word с результатами слияния.

Готово! Наслаждаемся сэкономленным временем!

UPDATE в ответ на комментарий alexey_lao :
При помощи несложного макроса можно сохранить каждый документ как отдельный файл.

Сохраняем наш шаблон как файл с расширением.docm (файл Word с поддержкой макросов).

Нажимаем сочетание клавиш Alt+F8 (вызов окна Макрос).

В открывшемся окне задаем "Имя" макроса (например, SaveFiles) и выбираем наш файл с шаблоном в выпадающем списке "Макросы из" (я предпочитаю создавать макросы непосредственно в файле, чтобы они работали и тогда, когда файл открыт на другом компьютере). Нажимаем кнопку "Создать".

В том месте, где мигает курсор, записываем код макроса:

Dim DocNum As Integer
For DocNum = 1 To ActiveDocument.MailMerge.DataSource.Reco rdCount
ActiveDocument.MailMerge.DataSource.Acti veRecord = DocNum
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = ActiveDocument.MailMerge.DataSource.Acti veRecord
.LastRecord = ActiveDocument.MailMerge.DataSource.Acti veRecord
End With
.Execute Pause:=False
End With
With ActiveDocument
.SaveAs FileName:="C://Test/" & DocNum , FileFormat:=wdFormatXMLDocument
.Close
End With

Вместо C://Test/ укажите адрес папки, в которой вы хотите сохранить документы.
Закрываем окно редактора Visial Basic и возвращаемся в наш документ с шаблоном.

Снова нажимаем Alt+F8, выбираем макрос SaveFiles в списке и нажимаем кнопку "Выполнить".

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

______________

Скачать файлы с примерами можно здесь:
http://hrexcel.ru/download/excel4hr_istochnik_dannyh.xlsx
http://hrexcel.ru/download/excel4hr_shablon.docx

Экспериментируйте!

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

Процедура слияния состоит из нескольких этапов.

1 этап. Подготовка данных электронной таблицы

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

• в таблице не должно быть объединенных ячеек. Вернее сказать так: ЕСЛИ в таблице есть объединённые ячейки, то надо быть готовым к тому, что при экспорте объединение будет отменено, и соответственно образуются лишние пустые строки и/или столбцы, что может нарушить структуру таблицы. В общем, объединённые ячейки - это зло:)
• все столбцы должны иметь уникальные названия, которые будут использоваться при слиянии. Если в таблице отсутствует первая строка с названиями столбцов, то её заменит первая строка данных, а значит, она в рассылке участвовать не будет.

В качестве примера возьмем таблицу с перечнем клиентов фитнес клуба "Экселент"

2 этап. Подготовка шаблона документа Word

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

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

Текст письма будет одинаковым за исключением обращения, номера клубной карты и даты окончания её действия. Эти данные будут импортироваться из таблицы Excel (выделено синим)

Таким образом, на этом этапе в документе Word печатается общий для всех писем текст.

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

3 этап. Работа Мастера слияния MS Word

Открываем файл письма в MS Word.

Проще всего осуществить слияние данных, следуя указаниям Мастера слияния. В версиях после Word2003 Мастер слияния запускается с помощью кнопки Начать слияние на вкладке Рассылки

В версиях до Word2007 следует выполнить команду меню Сервис -- Письма и рассылки -- Слияние. Кроме того, для более удобной работы версиях до Word2007 можно вывести панель инструментов Слияние

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

Работа Мастера слияния включает 6 шагов.

1 шаг: выбираем тип документа для рассылки, в нашем случае это Письма
	2 шаг: выбираем документ, на основе которого будут создаваться рассылки, им может быть открытый текущий документ, шаблон или существующий документ. При выборе варианта Шаблон или Существующий документ появляется возможность указать нужный файл в Проводнике. Мы выбираем Текущий документ
3 шаг: выбираем получателей. В нашем случае источником данных будет таблица Excel, значит отмечаем вариант Использование списка . Затем с помощью кнопки Обзор... выбираем нужный файл в Проводнике
		После выбора файла раскрывается диалоговое окно с выбранной таблицей. Если нам нужны все записи, то сразу нажимаем ОК. При необходимости можно список отсортировать, отфильтровать нужные записи, либо найти их с помощью соответствующих команд. Возможности фильтрации и поиска здесь, конечно, намного беднее, чем в Excel, но сделать простейшую выборку по текстовым или числовым значениям можно. Кроме того, возможно выбрать записи для рассылки вручную с помощью флажков:) В нашем случае требуется установить фильтр по полю Рассылка по значению "да" (значение "да" появляется в таблице, если срок действия клубной карты истекает в следующем месяце). Фильтр можно установить, щёлкнув мышкой по названию поля и выбрав "да", либо воспользовавшись ссылкой Фильтр в этом же диалоговом окне
4 шаг: вставляем нужные поля в документ. Прежде, чем выбрать один из предложенных Мастером вариантов работы, следует установить курсор в тексте туда, куда Вы хотите вставить данные. Если Вы забыли это сделать, тоже ничего страшного, поля можно вставить в любое место документа, а затем перенести. В нашем случае ставим курсор после слова "Уважаем" перед восклицательным знаком. Так как нам нужны отдельные поля, выбираем Другие элементы...
		Раскрывается диалоговое окно для выбора полей слияния. Выбираем поле Имя , нажимаем Вставить , то же самое для поля Отчество . Закрываем окно Вставка полей слияния и добавляем пробелы между вставленными полями. Если параметр Затенение полей установлен в положение Всегда , то вставленные поля будут отчетливо видны на сером фоне. Устанавливаем курсор после №, снова нажимаем ссылку Другие элементы... , выбираем № клубной карты - Вставить . Аналогично вставляем поле Дата окончания действия карты Кроме указанных выше полей требуется вставить окончание обращения ый(ая) , которое зависит от значения поля Пол . Для этого воспользуемся специальным полем, позволяющим вставлять одно из двух значений в зависимости от данных. Поставим курсор сразу после слова "Уважаем", нажмём кнопку Правила на вкладке Рассылки и выберем вариант IF...THEN...ELSE . В версиях до Word2007 аналогичная кнопка называется Добавить поле Word и находится на панели инструментов Слияние В раскрывшемся диалоговом окне зададим параметры После нажатия ОК, получим результат
5 шаг: просматриваем получившиеся письма, используя кнопки навигации. На этом шаге также можно изменить список получателей, применив фильтр или убрав флажки. Так как мы применили фильтр ранее, получателей осталось 3.
		Присмотревшись повнимательней к полученному результату, видим, что он не вполне соответствует нашим ожиданиям Номер клубной карты вместо 001768 отображается как 1768 , а дата и вовсе не по-нашему: сначала месяц, а потом день, хотя в таблице Excel всё было в порядке. Такие же неприятности могут возникнуть при импорте ячеек с десятичными числами, денежным форматом и т.д. Вывод неутешительный: при слиянии форматирование чисел и дат не сохраняется. Но выход есть! Поля документа Word, в которые вставляются наши данные, представляют собой код, который, во-первых, можно посмотреть, а во-вторых, изменить. Чтобы увидеть код поля, например, с номером клубной карты, следует кликнуть по нему правой кнопкой мыши (ПКМ) и выбрать команду Коды/Значения полей . Получим следующее { MERGEFIELD "M__клубной_карты" } Сам код изменять мы не будем, а вот формат допишем. Принцип добавления формата будет понятен любому, кто хоть немного знаком с созданием пользовательского формата в Excel. Чтобы число всегда состояло из шести цифр, формат должен состоять из шести нулей: { MERGEFIELD "M__клубной_карты" \# "000000" } . Теперь снова ПКМ по полю -- Обновить поле, и видим число в нужном формате. Аналогично поступаем с датой { MERGEFIELD "дата_окончания_действия_карты" \@ " DD . MM . YYYY " } И получаем окончательный вариант Подробнее о кодах полей Word можно прочитать в справочной системе Word или на

Приложение Word может извлекать данные для слияния из различных источников. Являясь частью набора Microsoft Office, приложение Word легко принимает данные из Outlook, Excel и Access. Можно использовать и другие источники, включая веб-страницы, текстовые файлы OpenDocument и файлы данных с разделителями, сохраненные в виде обычного текста. Если у вас еще нет источника данных, его можно создать в приложении Word.

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

Источники данных для слияния

Ниже перечислены некоторые источники данных, которые можно использовать для слияния в Word.

Другие файлы баз данных

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

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

Дополнительные сведения

Остались вопросы о слиянии в Word?

Помогите нам улучшить Word

У вас есть идеи по улучшению слияния или других функций Word? Поделитесь ими на странице

Теперь нужно создать источник данных слияния (адресов и фамилий получателей письма).

1. В разделе Выбор получателей окна Слияние области задач выберите пункт Создание списка (рис. 8.6).

Рис. 8.6. Окно Выбор получателей мастера Слияние

2. Щелкните на команде Создать в разделе Создание списка . Откроется окно диалога, показанное на рис. 8.7.

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

Некоторые наиболее распространенные поля заранее внесены в список окна создания источника данных. Вы можете удалить из базы данных ненужные поля и добавить свои собственные.

Рис. 8.7. Создание источника данных

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

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

3. Щелкните на кнопке Настройка . Откроется окно диалога, пока занное на рис. 8.8.

4. Выделите пункт Обращение .

5. Щелчком на кнопке Удалить сотрите выделенное поле.

Рис. 8.8. Настройка полей источника данных

Рис. 8.9. Окно диалога Получатели слияния

6. Повторяя шаги 4 и 5, удалите все поля, кроме Имя, Фамилия , Организация , Адрес 1 и Индекс .

7. Щелкните на кнопке ОК .

8. Щелкните на кнопке Закрыть окна диалога .

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

10. Вам обязательно придется когда-нибудь ввести информацию в источник данных. Щелкните на кнопке Изменить... , чтобы сделать это сейчас. Откроется форма для ввода данных. Давайте добавим несколько записей. Позже слияние сгенерирует ровно столько копий главного документа, сколько записей имеется в источнике данных.

11. Заполните поля формы.

12. Чтобы добавить еще одну запись, щелкните на кнопке Создать запись .

13. Введите еще несколько записей, перенеся в них информацию, представленную в табл. 8.1.

ТАБЛИЦА 8.1. Источник данных слияния

14. Щелкните на кнопке Закрыть , чтобы сохранить базу данных, а затем - на кнопке ОК окна Получатели .

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

Рис. 8.10 . Панель инструментов Слияние