The connection to the data source flies when merging. Preparing Documents for Bulk Changes: Merging a Word Template and an Excel Data Source

Often, spreadsheet data is required to be used when compiling various documents: reports, letters, contracts, etc. In this case, it is convenient to use the merge of MS Excel and MS Word data.

The merge procedure consists of several stages.

Stage 1. Preparing spreadsheet data

The table to be merged must meet some requirements:

• there should be no merged cells in the table. Rather, if the table contains merged cells, then you need to be prepared for the fact that the merge will be canceled during export, and, accordingly, extra empty rows and / or columns are formed, which may violate the table structure. In general, merged cells are evil :)
• all columns must have unique names that will be used in the merge. If the first row with the names of the columns is missing in the table, then the first row of data will replace it, which means that it will not participate in the mailing list.

As an example, let's take a table with a list of clients of the "Excelent" fitness club

Stage 2. Preparing a Word Document Template

At this stage, a document is formed in the text editor Word, into which the spreadsheet data will be embedded in the future. The text of this document is a common part for all mailings.

Suppose all customers whose club card expires next month are scheduled to receive notification emails.

The text of the letter will be the same except for the appeal, 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 the Word document.

For more convenient further work when merging, it is recommended to set the parameter Shading fields into position Is always to distinguish inserted merge fields from normal text. When enabled, fields are displayed with a gray background. Naturally, this background is not printed out.

Stage 3. Work of the MS Word Merge Wizard

Open the letter file in MS Word.

The easiest way to merge data is by following the instructions in the Merge Wizard. In versions after Word2003 The merge wizard startsusing the buttonStart merging in the tab Newsletters

In versions before Word2007, you should execute the menu commandService - Letters and Mailings - Merge.In addition, for more convenient operation versions before Word2007 you can bring up the Merge toolbar

Once again, I draw your attention to the fact that the letterhead contains only the text common to all letters, so the appeal looks like Respect! and the card number and date are missing.

The work of the Merge Wizard includes 6 steps.

Step 1: select the type of document for mailing, in our case it is Letters
	Step 2: select a 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, which means we mark the option Using a list... Then using the button Overview... 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 necessary records, or find them using the appropriate commands. The filtering and search capabilities here, of course, are much poorer than in Excel, but you can make the simplest selection by text or numeric values. In addition, it is possible to manually select records for distribution using checkboxes :) In our case, you need to install a filter by the field Newsletter by the value "yes" (the value "yes" appears in the table if the club card expires in the next month). The filter can be set by clicking on the field name and selecting "yes", or by using the Filter link in the same dialog box
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 the data. If you forgot to do this, that's okay too, fields can be inserted anywhere in the document, and then moved. In our case, we put the cursor after the word "Respect" before the exclamation mark. Since we need separate fields, we select Other items ...
		Opens a dialog box for selecting merge fields. Selecting a field Name, press Insert, the same for the field middle name... Close the window Insert merge fields and add spaces between the inserted fields. If the parameter Shading fields set to position Is always, the inserted fields will be clearly visible against a gray background. Set the cursor after the No., click the link again Other items ..., choose Club card number - Insert... Similarly, insert the field Card expiration date In addition to the above fields, you need to insert the end of the appeal th (th), which depends on the value of the field Floor... To do this, we will use a special field that allows you to insert one of two values ​​depending on the data. Put the cursor right after the word "Respect", press the button rules in the tab Newsletters and choose the option IF ... THEN ... ELSE... In versions prior to Word2007, the same button is called Add Word Field and is in the toolbar Merge In the opened dialog box, set the parameters After clicking OK, we get the result
Step 5: we look through the resulting letters using the navigation buttons. In this step, you can also change the recipient list by filtering or unchecking the 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 our way: 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! The fields of the Word document into which our data are 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 Field Codes / Values. We get the following (MERGEFIELD "M__club_card" } We will not change the code itself, but we will add the format. The principle of adding a format will be clear to anyone who is even a little familiar with creating a custom format in Excel. For a number to always be six digits, the format must be six zeros: (MERGEFIELD "M__club_card"\ # "000000"). Now RMB again on the field - Update field, and we see the number in the required format. We do the same with the date.(MERGEFIELD "card expiration date" \ @ " DD. MM. YYYY ") And we get the final version You can read more about Word field codes in the Word Help system or at

Word can extract data to be merged from a variety of sources. 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: Accessing named data sources at an HTTP location is not supported. Before using the file as a data source for a mail merge, save it to your local hard drive.

Data sources for merging

Listed below are some of the data sources that you can use for mail merge in Word.

Other database files

Other data sources can also be used for merging. To access these sources, run the Data Connection Wizard.

With your data source set up, you can create labels, envelopes, letters, and emails using mail merge.

additional information

Still have questions about a Word mail merge?

Help us improve Word

Do you have ideas for improving mail merge or other Word features? Share them on the page

From the article in the previous issue, we learned that the merge method allows you to create a data source using Word. In this article, we'll take a look at how data source information can be retrieved from an Excel spreadsheet.

The data source can be used in versions of Word 2000/2002/2003/2007, but the process of creating it is the same for all versions of Word. So, let's describe the algorithm for creating a data source:

1. Open Excel.
2. In the table that appears, in cell A1, enter the name of the first field (for example, country), in B1 - the second (for example, index) etc. (fig. 1).

Rice. 1. Data table

Using the tableWord 2000/2002

1. Open or create a main document in Word.
2. Merge button ( Merge Dialog Box).
3. Merge in the second step select the button Receive data, and from the list - Open data source(fig. 2).

Rice. 2. Drop-down list Open data source

1. In a new dialog Opening a data source from the dropdown File type choose BooksMSExcel.
2. Open the selected file by clicking the button Open.
3. In the dialog box that appears MicrosoftExcel(fig. 3) set the range or name.

Rice. 3. Window MicrosoftExcel

As a result, the file Excel became, as it were, "tied" as a data source, so now you can insert merge fields into the main document, and also merge the data source with the main document as explained in the previous article.

Rule for selecting records for inclusion in the merge process with the main document

The merge process can include records that meet certain predefined criteria. Links are used to select records OR, AND.

To set the selection criteria:

1. Open the main document.
2. Select from the toolbar Merge button ( Merge Dialog Box).
3. In the dialog box Merge in the third step select the button Selection records.

As a result, a dialog box will appear Selecting records.

1. In the tab Selecting records dialog box Selecting records in column Field from the drop-down list select the name of the field on the value of which the condition will be applied.
2. In the column Operator select a comparison operation.
3. In the column Meaning enter the value to compare with.
4. After setting all the criteria, close the window Selecting records by pressing the button OK(fig. 4) .

Rice. 4. Window Selecting records

1. In the dialog box Merge press the button To combine or on the toolbar Merge press the button ( Merge into a new document).

Using the tableExcel as a data source inWord 2003

1. On the menu Service select team Letters and mailings and then the command Merge.
2. In the opened task pane Merger, using the button Further, go to the third stage of the wizard Recipient selection, then click on the command
3. Find the required Excel spreadsheet file and open it by double-clicking on it.
4. Confirm that the first row of data contains the column headings (they are the merge fields) (Figure 5).

Rice. 5. Window Select table

Note. In the window Merge recipients you can edit the records.

1. Going to the fourth step, select the command
2. In the dialog box that appears Adding a merge field(Fig. 6) select a field from the list, after placing the mouse cursor in the place of the main document where the merge field should be located.

Rice. 6. Dialog box Adding a merge field

To commit about The selection of records for inclusion in the process of merging with the main document in Word 2003, it is necessary going to the third stage, select the command, and in the dialog box Merge recipients(fig. 7) uncheck the R checkbox next to the record to which you do not need to send the text of the main document.

Rice. 7. Dialog box Merge recipients

Using the tableExcel as a data source inWord 2007

1. In the tab Newsletter in a group Merge select team Select recipients.
2. Select (fig. 8), find the file you want in the dialog box Selecting a data source and open it.

Rice. 8. Team selection Use an existing list

Note. After selecting the file, the commands of the tab will become active

1. Click on Change the list of fields when the list changes.
2. Click on Insert merge field and select a field from the list by placing the mouse cursor in the place of the main document where the merge field should be located.

Selecting records for inclusion in the merge process with the main document in Word 2007

1. In the tab Newsletters in a group Start merging Press Select recipients and in the dropdown menu select Use an existing list.
2. Find the required Excel spreadsheet file and open it by double-clicking on it.
3. Confirm that the first row of data contains the column headers (which are the merge fields).
4. In the tab Newsletters in a group Start merging choose Change the list of recipients.

5. In the Mail Merge Recipients dialog box (Fig. 9), uncheck the R box next to the entry to which you do not need to send the text of the main document.

Rice. 9. Window Merge recipients

Key benefits of using Excel as a data source for the merge process

1. Saving time, because there is no need to do the same job many times.

2. Automation of the workflow by creating multiple copies of the document template with various details.

3. Convenience and ease of doing work, which consists of only three stages: creating the main document, creating a data source and directly merging them.

G.A. Serov, prof. RSUH

Now you need to create a data source for the mail merge (addresses and surnames of the recipients of the letter).

1. In the section Select recipients window Merge in the task pane, select List creation(fig. 8.6).

Rice. 8.6. Window Select recipients masters Merge

2. Click on the command Create In chapter List creation... The dialog box shown in Fig. 8.7.

The merge data source is a database of records, each of which will be used to generate a specific copy of the letter using the main document. All records have the same data fields.

Some of the more common fields are pre-listed in the Create Data Source window. You can remove unnecessary fields from the database and add your own.

Rice. 8.7. Creating a data source

Other section items Select 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 affect the merge result in any way, but it slows down the program.

3. Click the button Customization... The dialog box shown in Fig. 8.8.

4. Highlight the item Appeal.

5. By clicking 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, delete all fields except the Name, Surname, Organization, Address 1 and Index.

7. Click the button OK.

8. Click the button Close dialog boxes.

9. In the dialog box for saving the document that opens, enter the name Guests and click the button Save... The picture shown in Fig. 8.9 dialog box Merge recipients, in which there are currently no entries.

10. You will definitely need to enter information into the data source sometime. Click on the button Change... to do it now. A form for entering data 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 in the form fields.

12. To add another entry, click on the button Create record.

13. Enter a few more records, transferring the information presented in table. 8.1.

TABLE 8.1. Merge data source

14. Click the button Close to save the database and then on the button OK window Recipients.

Note To change the data source information, click in the toolbar Merge on the button Recipients, and then in the opened dialog window - on the button Change... The data source form will open. To navigate through the source records, use the buttons The first, Back, Forward, The last at the bottom of the form.

Rice. 8.10. Toolbar Merge

Now we will consider an example of how to implement merge Word data with data Microsoft SQL Server, at the same time, this process will be automated by means VBA Access 2003.

Many, 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 into some program.

Once I had the task of automating the merging of a certain Word template with data located on a SQL server, while all this had to be implemented into a program developed in Access 2003 (ADP project). And today I will show an example of solving this problem.

Initial data

And first, let's analyze the initial data, i.e. what we have.

So, as a client, as I said, we will have an Access 2003 ADP project. SQL Server 2012 Express will act as a data source for an example. Microsoft Office 2013 ( and Access 2003).

Let's create a test table on the server and fill it with data ( let's say in database Test). 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

Create a connection file to the MS SQL Server data source

Now let's create a connection file (ODC) to our data source. This file will act as a kind of " connection file template", Since later 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 considered 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 data source selection window, press the button " Create».

Then we enter the server address and click “ Further».

Then we select the database and the table for connection, I will remind you again, this is just a template, we will override all these parameters, click “ Further».

And in conclusion, we enter the understandable name of the connection file, and we can also immediately save it to the directory we need by pressing the button “ Overview", By default it is saved in « C: \ Users \ UserName \ Documents \ My Data Sources ". Click " Ready».

That's it, the file is created, Word can be closed without saving.

How to create a Word mail merge template

Now let's prepare a Word template i.e. this is the document into which we will substitute data from the SQL server database.

All preparation comes down to the fact that we need to insert the merge fields where we need it. This is done in the following way. Tab " Insert -> Quick 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). We press " 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 a lot of merge fields.

Access 2003 VBA Code to Merge Word Document with MS SQL Server Data Source

All that remains is to write the VBA code that will do the merge. For example, let's add a StartMerge button and a Price field to filter data on the form. Then, in the Visual Basic editor, write a procedure for merging, let's say with the name MergeWord, and in the event handler of the StartMerge button ( button press) insert the code for calling this procedure. The whole code will look like this ( I commented on it). Let me explain right away that the Word template and the ODC file are in the D: \ Test \ directory.

"Procedure for starting the 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 a data source (MS SQL Server) "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: = False Pause With "Close the template without saving WordDoc.close (wddonotsavechanges) Set WordDoc = Nothing Set WordApp = Nothing Else MsgBox" No merge template 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 merge procedure Call MergeWord (" D: \ Test \ Template.docx "," SELECT * FROM "" TestTable "" "& Filter &" " ) End Sub

We save and check the work.

After clicking on the (StartMerge) button, 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!