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 an Excel table.

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 Word versions. 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 the 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 required file Excel spreadsheet 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

Main advantages 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

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

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 an existing list" option and open the data source ( 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 before the closing one curly brace put a backslash followed by 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 "Change 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.”

Will be formed separate file Word with 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!

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. 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 and on the tab dialog box

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 indicate the type of sticker you need. 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. Application Merger Microsoft Office

Word saves the data source and field data. If you used the task pane

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.

On the tab Mail in Group Merger select team Selecting a data file.

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.

On the tab Mail in Group Merger select team To limit the list of recipients or use a subset of data file items, follow these steps:.

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 necessary records

. 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

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.

Edit list 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 large quantity 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.

Edit list 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.

Choose whether to edit the entire document set, only the copy displayed in currently, or a subset of documents identified by the 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.

The merger procedure consists of several stages.

Stage 1. Preparing Spreadsheet Data

A table to be merged must meet certain requirements:

• There should be no merged cells in the table. It would be more accurate to say this: IF there are merged cells in the table, then you need to be prepared for the fact that during export the merge will be canceled, and accordingly, extra empty rows and/or columns will be formed, which can disrupt the structure of the table. In general, merged cells are evil :)
• all columns must have unique names to be used when merging. If the table is missing the first row with the names of the columns, then it will be replaced by the first row of data, which means it will not participate in the distribution.

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

Stage 2. Preparing the template Word document

At this stage in text Word editor a document is generated into which the spreadsheet data will be subsequently embedded. 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 When 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.

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 :) 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
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 exclamation mark. Since we need separate fields, we select Other elements...
		A dialog box opens to allow you to select merge fields. 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 In the dialog box that opens, set the parameters After clicking OK, we get the result
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 in Excel spreadsheet everything was OK. The same troubles can arise when importing cells from decimal numbers, monetary 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 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. We get the following ( MERGEFIELD "M__club_card" } 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 You can read more about Word field codes in help system Word or on