Create a data source for mail merge in Word. Using Excel as a data source for the merge process Create a data source in Word

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 department information technologies on improving the personnel system: it is necessary to create printed forms- notification and 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.

Eat different ways To solve this problem, let's 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 with the results of the merger.

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!

Now we will look at an example implementation of how you can perform merger Word data 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 a source Excel data 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.

Initial data

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. Installed on the computer Microsoft Office 2013 (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

Create a connection file to the MS SQL Server data source

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 in a standard way let's create this file, 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.

Creating a Word Merge Template

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.

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

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 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 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 of the 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", 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!

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 look at how data source information can be obtained from Excel tables.

The data source can be used in versions of Word 2000/2002/2003/2007, but the process of creating it is the same for any version 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, a country), in B1 – second (for example, index) etc. (Fig. 1).

Rice. 1. Data table

Using a tableWord 2000/2002

1. Open or create a main document in Word.
2. Merger button ( Merge dialog box).
3. Merger in the second step select the button Get data, and from the list - Open data source(Fig. 2).

Rice. 2. Dropdown list Open data source

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

Rice. 3. Window MicrosoftExcel

The resulting file Excel has become, 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

Records that meet certain specified criteria can be included in the merge process. Links are used to select records OR, AND.

To set selection criteria:

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

As a result, a dialog box will appear Selection of records.

1. In the tab Selection of records dialog box Selection of 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 column Operator select comparison operation.
3. In column Meaning Enter the value to compare with.
4. After setting all the criteria, close the window Selection of records by pressing the button OK(Fig. 4) .

Rice. 4. Window Selection of records

1. In the dialog box Merger click the button Merge or on the toolbar Merger press the button ( Merging into a new document).

Using a tableExcel as a data source inWord 2003

1. On the menu Service select team Letters and mailings, and then the command Merger.
2. In the task pane that opens 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 headers (these are the merge fields) (Figure 5).

Rice. 5. Window Select table

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

1. Moving on to the fourth step, select the command
2. In the dialog box that appears Add a merge field(Fig. 6) select a field from the list, having first positioned the mouse cursor at the place in the main document where the merge field should be located.

Rice. 6. Dialog box Add a merge field

To commit about Selecting records to include in the process of merging with the main document in Word 2003 is necessary, going to the third stage, select the command, and in the dialog box Merge recipients(Fig. 7) uncheck the R box for the entry to which the text of the main document should not be sent.

Rice. 7. Dialog box Merge recipients

Using a tableExcel as a data source inWord 2007

1. On the tab Newsletter in Group Merger select team Select recipients.
2. Select (Fig. 8), find required file in the dialog box Selecting a data source and open it.

Rice. 8. Team selection Use existing list

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

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

Selecting records to include in the mail merge process in Word 2007

1. In the tab Newsletters in Group Start merge Press Select recipients and select from the menu drop-down list Use 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 (these are the merge fields).
4. In the tab Newsletters in Group Start merge select Change the list of recipients.

5.In the Merge Recipients dialog box (Fig. 9), uncheck the R box for the entry to which you do not want 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 perform the same work repeatedly.

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

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

G.A. Serova, prof. RSUH

7 Lecture No. 8

Merging documents.

Mail merge is used when you need to create a set of documents, such as address labels or letters on letterhead, that are sent to a large number of customers. Each letter or sticker contains both general and individual information. For example, the letter should address the customer by last name. The individual information for each letter or sticker comes from a data source.

The merge process consists of several general steps.

Setting up the main document. The main document contains text and graphics that are common to all versions of the master document, such as a return address or a salutation on letterhead.

Connecting a document to a data source. The data source is a file containing information that must be inserted into the document, such as the names and addresses of the recipients of the letter.

Refine the list of recipients or items. Microsoft application Office Word creates a copy of the master document for each data file item or record.

Adding text placeholders (merge fields) to a document When you merge, the merge fields are populated with data from the data file.

Previewing and completing a merge You can preview each copy before printing the entire set of copies of a document.

Tab commands are used to merge with mail Newsletters.

You can also merge using the task pane Merger, which allows you to carry out the entire process step by step. To use the task pane, in a group Start merge on the tab Newsletters select item Start merge, and then point Step-by-step Merge Wizard.

Setting up the main document

Launch the Word application.

By default, a blank document opens. Let it remain open. If you close it, the commands you need to perform in the next step will no longer be available.

On the tab Mail in Group Merger select team Start merge.

Click the document type you want to create.

For example, you can create the following document types.

Set of envelopes The return addresses on all envelopes are the same, but the recipient's address is unique in each case. Select an item Envelopes and on the tab Envelope options dialog box Envelope options Please indicate your preferences regarding envelope size and text formatting.

Set of address stickers Each sticker contains a name and address that are unique. Select an item stickers and in the dialog box Sticker options please indicate desired type stickers.

Set of documents on letterhead or messages Email The basic content of all letters or messages is the same, but each of them contains information intended for a specific recipient, for example, name, address or some other information. To create these types of documents, select letters or emails.

Directory or folder Each item displays the same type of information, such as a name or description, but it is unique to each item. Click catalog to create this type of document.

Resumption of merger

If you need to interrupt your merge work, you can save the main document and resume the merge later. Microsoft Office Word saves the data source and field data. If you used the task pane Merger, Word will return to the point where you want to resume the merge.

When you're ready to resume the merge, open the document.

Word will ask for confirmation to open the document, which will run the SQL command.

Since this document is connected to a data source, click Yes. The text of the document appears along with all inserted fields.

Connecting a document to a data source

To perform a data merge in a main document, you must connect the document to a data source or data file.

Selecting a data file

On the tab Mail in Group Merger select team Select recipients.

Follow these steps:

Open the application document Microsoft Word. This document must contain one table. The first row of the table should contain the headings, and the remaining rows should contain the entries that should be included in the compound document. You can also use a header data source as a data source (Header data source. A document containing a header row (or record) that is used to merge the specified data source with the main merge document.).

Refining the list of recipients or items

If you are connecting to a specific data file, you may not want to transfer the data of all data file records to the main document.

To limit the list of recipients or use a subset of data file items, follow these steps:

On the tab Mail in Group Merger select team Edit list.

In the dialog box Merger destinations

Selecting individual records This method is most useful if the list is short. Select the check boxes next to those recipients who should be included in the list, and clear the check boxes next to those recipients who should be excluded from the list.

If you know that you only want to include a few records in the master document, you can clear the check box in the title bar and then select only the records you want. Likewise, if you want to include most of the list in your document, select the check box in the title bar, and then uncheck the entries that you don't want to include.

Sorting entries Click the column header of the item you want to sort by. The list is sorted alphabetically in ascending order (A to Z).

Filtering entries This feature is useful if the list contains entries that you do not want to see or include in the master document.

Add text placeholders (merge fields) to a document

After you connect the main document to the data file, you can enter document text and add text placeholders to indicate where unique data should appear in each copy of the document.

Text placeholders, such as an address or salutation, are called merge fields. The fields in Word correspond to the highlighted column headings in the data file.

The data file columns represent categories of data. The fields added to the main document are text placeholders for these categories.

Data file lines represent data records. When you perform a mail merge, Word creates a copy of the main document for each record.

When placing a field in the main document, it is implied that a certain category of data, such as a last name or address, should appear at its location.

Note. When you insert a merge field into the main document, the field name is always enclosed in double angle brackets (""). In compound documents, these brackets are not used for emphasis. They only allow you to distinguish fields in the main document from normal text.

What happens during a merger

When merging, the data in the first row of the data file replaces the fields in the main document, thereby forming the first composite document. The data in the second row of the data file replaces the fields to form a second compound document, and so on.

Examples of working with fields

You can add any column heading from the data file to the main document as a field. This gives you some flexibility when creating letterheads, labels, emails, and other compound documents.

You can combine fields and separate them using punctuation marks. For example, to generate an exact address, you need to insert the following fields into the main document:

"First Name Last Name"

"House and Street"

"City", "Region", "Postal Code"

For common use cases, such as address blocks or greeting lines, Word provides complex fields—a combination of multiple fields. Examples are listed below.

The Address Block field is a combination of several fields, including first name, last name, house and street, city and zip code.

The Greeting Line field may include one or more name fields, depending on the greeting words used.

The content of each of the complex fields can be customized accordingly. For example, you can choose a formal name for the address (Mr. Andrey Alfirovich Jr.), and use “For” instead of “Dear” in your address.

Mapping Merge Fields to a Data File

To ensure that Word will find a column in the data file that corresponds to each address or greeting element, you must map the mail merge fields in Word to the columns in the data file.

To match the fields, select Field selection in Group Compose a document and insert fields on the tab Newsletters.

A dialog box will open Field selection.

The address and salutation elements appear on the left. Column headings from the data file appear on the right.

Word looks for the appropriate column for each element. The illustration shows that Word automatically found a match for the column Surname data file field Surname. But he was unable to contrast the other elements with each other. Specifically, from this data file, Word did not find a match for the field Name.

In the list on the right, you can highlight the data file column corresponding to the element on the left. Now the illustration shows that the column Name matches the field Name. There's nothing wrong with the fields Appeal, Unique identificator And Surname no match found. A master document does not need to use every field. If you add a field for which there is no data in the data file, it will appear in the master document as an empty text placeholder—usually an empty string or a hidden field.

Entering content and adding fields

In the main document, click where you want to insert the field.

Use the group Compose a document and insert fields on the tab Newsletters.

Add any of the following items.

Address block with name, postal address and other information

Select an item Address block.

In the dialog box Inserting an address block select the address elements and display formats you want, and then click OK.

Field selection, then Word couldn't find some information needed for the address block. Click the arrow next to (no match)

Welcome line

Select an item Welcome line.

Select a salutation line format that includes the salutation, the name, and the punctuation mark that follows the name.

Select greeting line text for cases where the recipient's name is not available, for example, when the data source does not contain the recipient's first or last name but only the company name.

Click the button OK.

If a dialog box appears Field selection, then Word couldn't find some information needed for the greeting line. Click the arrow next to (no match), and then highlight the corresponding field in the data source that you want to merge.

Individual fields

Information can be inserted from individual fields, such as last name, phone number, or donor contribution amount. To quickly add a field from a data file to the main document, click the arrow next to the list Inserting a data field, and then select a field name.

To use more options when inserting individual fields into a document, follow these steps:

On the tab Mail in Group Recording and adding fields select team Insert merge field.

In the dialog box Inserting a data field Perform one of the following actions.

To select address fields that will automatically match the data source fields even if the data source fields have different names, select Address fields.

To highlight fields that always use data directly from a data file column, select Database fields.

On the list Fields Click the required field.

Click the button Insert, and then Close.

If a dialog box appears Field selection, then Word couldn't find some of the information it needed to insert the field. Click the arrow next to (no match), and then highlight the corresponding field in the data source that you want to merge.

• Custom fields from Outlook contact list

The only way to include custom contact list fields in the main document is to start a mail merge from Outlook. First you need to configure the display of the contact list using exactly those fields that should be used during the merge. After this you can start merging. Once all the necessary settings have been made, Word will automatically open and you can complete the mail merge.

Formatting Merge Results

To format the data contained in a document, select the mail merge field and format it the same way you format any other text. The selection must include double chevron quotes ( « » ) surrounding the field.

Preview and complete the merge

Once all the fields have been added to the main document, you can preview the merge results. Before the merge is actually completed, you can review the merged documents and make appropriate changes if necessary.

To view, do one of the following in a group View results tabs Newsletters :

Click View results.

Browse through each compound document page by page using the buttons Next entry And Previous post in Group View results.

View a specific document using the command Find recipient.

Completing the merger

Multiple documents can be printed or edited individually. This can be done for all documents or for a subset of documents.

Select whether to edit the entire set of documents, just the copy currently displayed, or a subset of documents identified by a specified record number. Word saves the copies you want to edit as a single file, separated by page breaks.

Saving the main document

Remember that compound documents are saved separately from the main document. It is useful to keep the main document separate because it may be useful when performing subsequent merges.