Слияние документа Word с источником данных Microsoft SQL Server с помощью VBA Access 2003

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

Слияние Word с SQL Server

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

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

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

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

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

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

Начинающим программистам SQL рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно рассказываю про многие полезные конструкции и функции языка T-SQL.

   
   CREATE TABLE dbo.TestTable(
        ID INT IDENTITY(1,1) NOT NULL,
        ProductName VARCHAR(50) NOT NULL,
        Price MONEY NULL,
   CONSTRAINT [PK_TestTable] 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

Скриншот 1

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

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

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

Переходим на вкладку рассылки и в меню «Выбрать получателей» выбираем «Использовать существующий список».

Скриншот 2

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

Скриншот 3

Далее выбираем тип источника данных, т.е. Microsoft SQL Server. Жмем «Далее».

Скриншот 4

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

Скриншот 5

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

Скриншот 6

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

Скриншот 7

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

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

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

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

Скриншот 8

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

Скриншот 9

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

Скриншот 10

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

Скриншот 11

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

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 6
  1. Аватар
    Павел

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

    1. Админ
      Админ (автор)

      В ADP проекте Access в объектах выбрать «Формы», а затем нажать «Создать». Пример создания формы можете найти вот здесь

  2. Аватар
    Павел

    Пардон. Я подумал, что код VBA нужно писать в Word, а не в Access.

  3. Аватар
    Александр

    Добрый день. Помогите пожалуйста. Не получается запустить Ваше приложение

    1. Админ
      Админ (автор)

      Поясните в чем проблема? Какие ошибки возникают?

  4. Админ
    Админ (автор)

    Цитата:Добрый вечер. Спасибо за оперативный ответ. Создал новую базу Access. В ней Создал форму и вставил код.После нажатия кнопки выводится сообщение: » В коллекции не удается найти элемент, соответствующий имени или порядковому номеру. На всякий случай высылаю папку с файлами, которые расположены у меня на d:\test
    Здравствуйте.
    В шаблоне Word должны быть «поля слияния» с названием, которые соответствуют названию полей в источнике данных (в Вашем случае полям в таблице TestTable). У Вас ошибка связана именно с этим.
    В присланном Вами шаблоне отсутствуют поля слияния. Для настройки шаблона прочитайте раздел «Создаем шаблон Word для слияния» текущей статьи.

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.