Now we will look at an example implementation of how you can perform Word data merge with data Microsoft SQL Server, in this case, this process will be automated by means VBA Access 2003.
Many people probably already know how to merge Word documents, for example, with an Excel data source or with the same SQL server, but not everyone knows how to automate this process or implement it in some program.
One day I was faced with the task of automating the merging of a certain Word template with data located on a SQL server, and all this had to be implemented in a program developed in Access 2003 (ADP project). And today I will show an example of solving this problem.
And first, let's look at the initial data, i.e. what we have.
So, as a client, as I already said, we will have an Access 2003 ADP project. The data source for the example will be SQL Server 2012 Express. Microsoft Office 2013 is installed on your computer ( and Access 2003).
Let's create a test table on the server and fill it with data ( valid in the Test database). To do this, you can run the following SQL statement.
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 ("Computer", 500) GO INSERT INTO dbo.TestTable(ProductName, Price) VALUES ("Monitor", 400) GO INSERT INTO dbo.TestTable(ProductName, Price) VALUES ("Phone", 200) GO INSERT INTO dbo. TestTable(ProductName, Price) VALUES ("Tablet", 300) GO INSERT INTO dbo.TestTable(ProductName, Price) VALUES ("Printer", 250) GO SELECT * FROM TestTable
Now let's create a connection (ODC) file to our data source. This file will act as a kind of “ connection file template", since subsequently we can and will redefine both the database and the SQL query itself.
To create a connection file to the SQL server, let's open Word and create this file in the standard way, i.e. using the functionality " Newsletters». ( By the way, we have already looked at an example of creating a connection to a SQL server from Excel in the material - Excel - Connecting and receiving data from a SQL server).
Then in the window for selecting a data source, click the button “ Create».
Then enter the server address and click “ Further».
Then we select the database and table to connect, let me remind you again, this is just a template, we will override all these parameters, click “ Further».
And finally, we enter a clear name for the connection file, and we can also immediately save it to the directory we need by clicking the “ Review", by default it is saved in « C:\Users\UserName\Documents\My Data Sources." Click " Ready».
That's it, the file has been created, we can close Word without saving.
Now let's prepare the Word template i.e. This is the document into which we will insert data from the SQL server database.
All preparation comes down to the fact that we need to insert merge fields where we need them. This is done as follows. Tab " Insert -> Express Blocks -> Field».
Looking for a field MERGEFIELD and enter the name of the field that will correspond to the field in the data source ( in my case it is ProductName and Price). Click " OK».
Since I have this test template, there will be practically no text in it, and I will display only two fields, you will most likely have a lot of text and many merge fields.
All that remains is to write the VBA code that will perform the merge. For example, let's add a StartMerge button and a Price field to the form to filter the data. Then in the Visual Basic editor we will write a procedure for merging, for example with the name MergeWord, and in the event handler of the StartMerge button ( button press) insert the code for calling this procedure. The entire code will look like this ( I commented on it). Let me clarify right away that I have the Word template and the ODC file in the D:\Test\ directory.
"Procedure for running a merge Private Sub MergeWord(TemplateWord As String, QuerySQL As String) "First parameter - Path to the Word template "Second parameter - Database query string On Error GoTo Err1 Dim ConnectString As String, PathOdc As String Dim WordApp As Object Dim WordDoc As Object "ODC file template for data connection PathOdc = "D:\Test\TestSourceData.odc" If TemplateWord<>"" Then "Create a Word document Set WordDoc = CreateObject("Word.document") Set WordDoc = GetObject(TemplateWord) Set WordApp = WordDoc.Parent "Create a connection to the data source (MS SQL Server) "We take some data from the current ADP connection project 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;" "Set the data source WordDoc.MailMerge.OpenDataSource NAME:=PathOdc, _ Connection:=ConnectString, _ SQLStatement:=QuerySQL "Make Word visible WordApp.Visible = True WordApp.Activate "Start merging With WordDoc.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute Pause:=False End With "Close the template without saving WordDoc.close (wddonotsavechanges) Set WordDoc = Nothing Set WordApp = Nothing Else MsgBox "No template to merge is specified", vbCritical, "Error" 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 = "" "Condition If Nz(Me.Price, "")<>"" Then Filter = "WHERE Price >= " & Me.Price End If "Call the merge procedure Call MergeWord("D:\Test\Template.docx", "SELECT * FROM ""TestTable"" " & Filter & " " ) End Sub
We save and check the work.
After clicking on the button (StartMerge), Word will start, in which all the data has already been filled in and there will be as many documents as there are lines in the source.
As you can see, everything works. That's all for me, I hope the material was useful. Bye!
Recently I saw a wild picture in one organization :)
The company was moving to a new building - from Moscow Region to Moscow. We are talking, accordingly, about changing the essential terms of the employment contract for all employees. According to the procedure, employees must be notified against signature of the upcoming move, and then additional agreements to their employment contracts must be concluded with everyone. The organization employs about 1.5 thousand people, the HR department must urgently prepare documents.
What happens next?
The head of the HR department sends a request to the information technology department to finalize the personnel system: you need to create printed forms - a notification and an additional agreement. IT responds that such an improvement will cost so many thousands of euros, and the costs are not included in the budget, and, among other things, implementation is possible no earlier than in two months.
Sending curses at the IT department, HR officers go to work on weekends, leaving small children at home. An immortal labor feat: 3,000 documents were created in two days! The name and passport details of each employee were manually added to the template, and so on 3000 times!
Will they be able to forgive themselves for wasting their time so mediocrely when they find out that using Mail Merge template in Word with an Excel table containing personal data, this could be done in about twenty minutes :)
STEP 1. Create a document template in Word.
The data that will change is highlighted in red: last name, first name, patronymic, number, date of the employment contract, position, department and endings in the words respected and received depending on gender.
STEP 2. Create a data source in Excel.
After we set up Merge, the program will substitute data from the source into the template. Each line is a separate document.
STEP 3. Return to the document template that we created in Word. We have to work with the “Mailouts” tab, study it carefully.
Click the "Select recipients" button, select the "Use existing list" option and open the data source (an Excel file with information about employees).
In the next window, select the file sheet that contains the necessary data.
STEP 4. Place the cursor in the place in the template text where you want to insert data from the source, click the “Insert merge field” button and select the desired field.
This is what my template looks like after all the merge fields are inserted:
STEP 5. Create Rules that change the endings of the words “dear” and “received” depending on the gender of the employee.
Place the cursor after the end of the word. Click the "Rules" button and select "IF...THEN...ELSE" in the drop-down list.
We write down the rule: if the gender is female - then..., otherwise...
STEP 6. Format the dates.
If you've used Mailings (Merge) before, you've probably encountered the fact that dates transferred from Excel look completely different in Word. For example, instead of 06/19/2012 you will most likely see 6/19/2012; this result is unlikely to suit you.
There are different ways to solve this problem, we will use the simplest and most convenient one.
Let's press the key combination Alt+F9, now we can see the codes of the merge fields.
Inside the code of the Contract_Date field, we put a backslash before the closing curly brace, and after it a formatting key:
( MERGEFIELD "Contract_date" \@ "DD.MM.YYYY"
}
@ - date designation, "DD.MM.YYYY" - date format key of the form 19.06.2012
.
You can use any other format. For example, so that the date looks like June 19, 2012, use the key \@ "DD MMMM YYYY".
To exit code editing mode, press Alt+F9 again.
STEP 7. Click the “View Results” button to check what happened.
STEP 8. Editing the list of employees.
By clicking on the "Edit list of recipients" button, you can exclude individual entries from the list, check for duplicates, set sorting or filters.
You should pay special attention to sorting so that you don’t have to waste time sorting documents after they are printed. It is most convenient to sort by employee names or department names.
STEP 8. Complete the merger.
Click the "Find and Merge" button. If you select the Print Documents option, the documents will be sent directly to the printer. If you need to review documents and make edits before printing, select “Edit individual documents.”
A separate Word file will be generated with the merge results.
Ready! Enjoy the time you save!
UPDATE in response to comment alexey_lao
:
Using a simple macro, you can save each document as a separate file.
We save our template as a file with the extension .docm (Word file with macro support).
Press the key combination Alt+F8 (calling up the Macro window).
In the window that opens, set the “Name” of the macro (for example, SaveFiles) and select our file with the template in the “Macros from” drop-down list (I prefer to create macros directly in the file so that they work even when the file is open on another computer). Click the "Create" button.
In the place where the cursor blinks, write the macro code:
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
Instead of C://Test/, enter the address of the folder where you want to save the documents.
We close the Visial Basic editor window and return to our document with the template.
Press Alt+F8 again, select the SaveFiles macro in the list and click the "Run" button.
While the computer creates and saves each document in turn, you can have a cup of coffee and a cookie. Or go home to sleep if there are more than a couple hundred documents :)
You can download files with examples here:
http://hrexcel.ru/download/excel4hr_istochnik_dannyh.xlsx
http://hrexcel.ru/download/excel4hr_shablon.docx
Experiment!
The merger procedure consists of several stages.
Stage 1. Preparing Spreadsheet Data
A table to be merged must meet certain requirements:
As an example, let's take a table with a list of clients of the Excellent fitness club.
Stage 2. Preparing a Word Document Template
At this stage, a document is generated in the Word text editor into which the spreadsheet data will be embedded in the future. The text of this document is a part common to all mailings.
Suppose we plan to send notification letters to all customers whose club card expires next month.
The text of the letter will be the same except for the address, club card number and expiration date. This data will be imported from an Excel spreadsheet (highlighted in blue)
Thus, at this stage, the text common to all letters is printed in a Word document.
For more convenient further work during merging, it is recommended to set the parameter Field shading to position Always to distinguish inserted merge fields from plain text. When enabled, fields appear with a gray background. Naturally, this background is not printed.
Stage 3. MS Word Merge Wizard at work
Open the letter file in MS Word.
The easiest way to merge data is to follow the Merge Wizard. In versions after Word2003 Merge Wizard startsvia buttonStart merge on the tab Newsletters
In versions before Word2007 you should execute the menu commandService - Letters and mailings - Merger.In addition, for more convenient operation versions before Word2007 you can display the Mail Merge toolbar
Once again, please note that the letter form contains only text common to all letters, so the appeal looks like We respect you!, and the card number and date are missing.
The Merge Wizard works in 6 steps.
In our case, we need to set a filter by field Newsletter by value "yes" (the value "yes" appears in the table if the club card expires next month). The filter can be set by clicking on the field name and selecting “yes”, or using the Filter link in the same dialog box Select a field Name, press Insert, same for the field Surname. Closing the window Inserting Merge Fields and add spaces between the inserted fields. If the parameter Field shading set to position Always, the inserted fields will be clearly visible against the gray background. Place the cursor after the number, click the link again Other elements..., select Club card number - Insert. Insert the field in the same way Card expiration date In addition to the above fields, you must insert the end of the request th, which depends on the field value Floor. To do this, we will use a special field that allows you to insert one of two values depending on the data. Place the cursor immediately after the word “Respect” and press the button Rules on the tab Newsletters and select the option IF...THEN...ELSE. In versions prior to Word2007, a similar button is called Add a Word field and is located on the toolbar Merger The fields of the Word document into which our data is inserted are code that, firstly, can be viewed, and secondly, changed. To see the field code, for example, with the club card number, you should right-click on it (RMB) and select the command Codes/Field Values. Step 1: select the type of document to be sent, in our case it is Letters
Step 2: select the document on the basis of which mailings will be created; it can be an open current document, a template or an existing document. When choosing an option Sample or Existing document it becomes possible to specify the desired file in Explorer. We choose Current document
Step 3: select recipients. In our case, the data source will be an Excel table, so check the option Using a list. Then using the button Review... select the desired file in Explorer
After selecting a file, a dialog box opens with the selected table. If we need all the records, then immediately click OK. If necessary, you can sort the list, filter the required records, or find them using the appropriate commands. The filtering and search capabilities here are, of course, much poorer than in Excel, but you can make a simple selection based on text or numeric values. In addition, it is possible to select posts for distribution manually using checkboxes :)
Step 4: insert the required fields into the document. Before choosing one of the options offered by the Wizard, you should place the cursor in the text where you want to insert data. If you forgot to do this, that’s okay, the fields can be inserted anywhere in the document and then moved. In our case, we place the cursor after the word “Respect” before the exclamation mark. Since we need separate fields, we select Other elements...
A dialog box opens to allow you to select merge fields.
In the dialog box that opens, set the parameters 
Step 5: We view the resulting letters using the navigation buttons. At this step, you can also change the list of recipients by applying a filter or clearing check boxes. Since we applied the filter earlier, there are 3 recipients left.
Taking a closer look at the result obtained, we see that it does not quite meet our expectations 
Club card number instead 001768
displayed as 1768
, and the date is not at all like ours: first the month, and then the day, although everything was in order in the Excel table. The same troubles can arise when importing cells with decimal numbers, currency format, etc. The conclusion is disappointing: when merging, the formatting of numbers and dates is not preserved. But there is a way out!
We get the following
We will not change the code itself, but we will add to the format. The principle of adding a format will be clear to anyone who is even slightly familiar with creating a custom format in Excel. To ensure that a number always contains six digits, the format must consist of six zeros:
( MERGEFIELD "M__club_card"\# "000000" ) . Now right-click on the field again - Update Field, and we see the number in the required format.
We do the same with the date.( MERGEFIELD "card_expiration_date" \@ " D.D. MM. YYYY " )
And we get the final version
More information about Word field codes can be found in Word Help or at
Word can pull merge data from a variety of sources. As part of the Microsoft Office suite, Word easily accepts data from Outlook, Excel, and Access. Other sources can be used, including web pages, OpenDocument text files, and delimited data files saved as plain text. If you don't already have a data source, you can create one in Word.
Important: Access to named data sources in an HTTP location is not supported. Before using a file as a data source for a merge, save it to your local hard drive.
Below are some data sources that you can use for mail merge in Word.
You can also use other data sources for merging. To access these sources, run the Data Connection Wizard.
Once you set up your data source, you can create labels, envelopes, letters, and emails using mail merge.
Do you have ideas for improving mail merge or other Word features? Share them on the page
Now you need to create a merge data source (addresses and last names of email recipients).
1. In section Selecting Recipients window Merger task pane, select Creating a list(Fig. 8.6).
Rice. 8.6. Window Selecting Recipients masters Merger
2. Click on the command Create In chapter Creating a list. A dialog box will open, shown in Fig. 8.7.
The merge data source is a database consisting of records, based on each of which a specific copy of the letter will be generated using the main document. All records have the same data fields.
Some of the most common fields are pre-listed in the data source creation window. You can remove unnecessary fields from the database and add your own.
Rice. 8.7. Creating a Data Source
Other section items Selecting Recipients allow you to select an existing database, previously created in Word or in an application such as Access, or an address book as a data source.
Note The presence of extra fields in the data source does not in any way affect the result of the merge, but it slows down the program.
3. Click the button Settings. A dialog box will open, shown in Fig. 8.8.
4. Highlight an item Appeal.
5. Click the button Delete erase the highlighted field.
Rice. 8.8. Setting up data source fields
Rice. 8.9. Dialog window Merge recipients
6. Repeating steps 4 and 5, remove all fields except Name. Surname, Organization, Address 1 And Index.
7. Click the button OK.
8. Click the button Close dialog windows.
9. In the dialog box for saving the document that opens, enter the name Guests and click on the button Save. The image shown in Fig. 8.9 dialog box Merge recipients, which currently has no entries.
10. You will definitely have to enter information into a data source at some point. Click the button Change... to do it now. A data entry form will open. Let's add some entries. Later, the merge will generate exactly as many copies of the master document as there are records in the data source.
11. Fill out the form fields.
12. To add another entry, click the button Create an entry.
13. Enter a few more entries, transferring into them the information presented in the table. 8.1.
TABLE 8.1. Merge data source
14. Click on the button Close to save the database and then on the button OK window Recipients.
Note To change data source information, click in the toolbar Merger on the button Recipients, and then in the dialog window that opens - on the button Change. The data source form will open. To navigate through the source records, use the buttons First, Back, Forward, Last at the bottom of the form.
Rice. 8.10. Toolbar Merger
