Data sources that can be used for mail merge. Merge Microsoft Word documents Failed to open data source during merge

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 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 appeared table in cell A1, enter the name of the first field (for example, country), in B1 - the second (for example, index) etc. (fig. 1).

Figure: 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 Get data,and from the list - Open data source(fig. 2).

Figure: 2. Drop-down list Open data source

1. In a new dialog Opening a data source from the dropdown File type select 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.

Figure: 3. Window MicrosoftExcel

As a result, the file Excelbecame, 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 column Operator select a comparison operation.
3. In column Value enter the value to compare with.
4. After specifying all the criteria, close the window Selecting recordsby pressing the button OK(fig. 4) .

Figure: 4. Window Selecting records

1. In the dialog box Merge press the button To combineor 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 mailingsand 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).

Figure: 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.

Figure: 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 necessarygoing 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.

Figure: 7. Dialog box Merge recipients

Using the tableExcel as a data source inWord 2007

1. In the tab Newsletter in Group Merge select team Select recipients.
2. Select (fig. 8), find the file you want in the dialog box Data source selectionand open it.

Figure: 8. Team selection Use an existing list

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

1. Click on Change field listwhen the list changes.
2. Click on Insert merge field and select a field from the list, after placing the mouse cursor in the place of the main document, where the merge field should be.

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

1. In the tab Newsletters in Group Start merging Press on 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 Group Start merging select 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.

Figure: 9. Window Merge recipients

Key benefits of using Excel as the 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 work, which consists of only three stages: creating the main document, creating a data source and merging them directly.

G.A. Serov, prof. RSUH

Now we will look at an example of how to implement merge Word data with data Microsoft SQL Server, while 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 a task to automate the merging of a certain Word template with data located on a SQL server, while 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 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", Because 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 a».

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 a clear name for 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 " Done».

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

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 as follows. The " Insert -\u003e Quick Blocks -\u003e 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 will have practically no text in this test template, and I will display only two fields, you will most likely have a lot of text and many 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 \u003d" D: \\ Test \\ TestSourceData.odc "If TemplateWord<> "" Then "Create a Word document Set WordDoc \u003d CreateObject (" Word.document ") Set WordDoc \u003d GetObject (TemplateWord) Set WordApp \u003d WordDoc.Parent" Create a connection to a data source (MS SQL Server) "We take some data from the current ADP connection Project ConnectString \u003d "Provider \u003d SQLOLEDB.1; "& _" Integrated Security \u003d SSPI; "& _" Persist Security Info \u003d True; "& _" Initial Catalog \u003d "& CurrentProject.Connection.Properties (" Initial Catalog ") &"; "& _" Data Source \u003d "& CurrentProject.Connection.Properties (" Data Source ") &"; "& _" Use Procedure for Prepare \u003d 1; "& _" Auto Translate \u003d True; "& _" Packet Size \u003d 4096; "& _" Use Encryption for Data \u003d False; "" Set the data source WordDoc.MailMerge.OpenDataSource NAME: \u003d PathOdc, _ Connection: \u003d ConnectString, _ SQLStatement: \u003d QuerySQL "Make Word Visible WordApp.Visible \u003d True WordApp.Activate" Start Merging With WordDoc.MailMerge .Destination \u003d wdSendToNewDocument .SuppressBlankLines \u003d True .Exesecute Pause With "Close the template without saving WordDoc.close (wddonotsavechanges) Set WordDoc \u003d Nothing Set WordApp \u003d 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 \u003d Nothing Set WordApp \u003d Nothing Resume Ex1 End Sub Private Sub StartMerge_Click () Dim Filter As String Filter \u003d "" "Condition If Nz (Me.Price," ")<> "" Then Filter \u003d "WHERE Price\u003e \u003d" & 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 is already 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. While!

7 Lecture number 8

Merging documents.

Merge is used when you need to create a set of documents, for example, stickers with addresses or letters on forms that are sent to a large number of customers. Each letter or sticker contains both general and individual information. For example, the letter should contain an appeal to the customer by last name. Individual information for each letter or sticker comes from the 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 letterhead greeting.

Connecting a document to a data source. The data source is a file containing information to 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 Office Word creates a copy of the main document for each item or entry in the data file.

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

Previewing and completing the merge Before printing an entire set of copies of a document, you can preview each copy.

For mail merge use the tab commands Newsletters.

Merging can also be done using the task pane Mergewhich allows you to complete the entire process step by step. To use the task pane, in the group Start merging in the tab Newsletters select item Start mergingand then paragraph Step by step merge wizard.

Setting up the main document

Start Word.

By default, a blank document opens. Keep it open. If you close it, the commands that need to be executed in the next step are no longer available.

In the tab post office in Group Merge select team Start merging.

Click the type of document you want to create.

For example, you can create the following types of documents.

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

Set of address stickers Each sticker contains a name and address, which are unique. Select item stickers and in the dialog Sticker options specify the type of sticker you want.

Set of paperwork or e-mails The main content of all letters or messages is the same, but each of them contains information intended for a specific recipient, such as last name, address, or some other information. To create these types of documents, select letters or emails.

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

Merge resumption

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

When you're ready to resume the mail 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 document text appears, along with any inserted fields.

Connecting a document to a data source

To merge data in a master document, you must connect that document to a data source or data file.

Selecting a data file

In the tab post office in Group Merge select team Select recipients.

Follow these steps.

Open a Microsoft Word application document. This document should contain one table. The first row of the table should contain headers, and the remaining rows should contain records to be included in the master 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 combine the specified data source with the main mail merge document.).

Refine the list of recipients or items

When connecting to a specific data file, you may not want to migrate the data of all records of the data file to the main document.

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

In the tab post office in Group Merge select team Change list.

In the dialog box Merge destinations

Highlighting individual records This method is most useful when the list is short. Select the checkboxes next to those recipients that should be included in the list, and uncheck the boxes next to those recipients that should be excluded from the list.

If you know that only a few records need to be included 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 boxes for the entries you do not want to include.

Sorting records Click the column heading of the item you want to sort by. The list will be sorted in ascending alphabetical order (A to Z).

Filtering records 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 your document

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

Placeholder text such as an address or greeting are called merge fields. The fields in Word correspond to the selected column headings in the data file.

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

Data file lines are 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 assumed that a certain category of data should appear at its location, for example, a name or an address.

Note. When you insert a mail 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 in a merge

In a merge, the data in the first line of the data file replaces the fields in the main document, thereby forming the first compound document. The data on the second line of the data file replaces the fields to form a second compound document, and so on.

Examples of working with fields

Any column header can be added as a field from the data file to the main document. This allows some flexibility in drafting letterheads, stickers, e-mails, and other compound documents.

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

"First Name Last Name"

"House and street"

"City", "Region", "Zip 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 String field can 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, for an address, you can choose a formal name (Mr. Andrey Alfirovich Jr.), and use “For” instead of “Dear” in the address.

Mapping merge fields to a data file

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

To match the fields, select Selection of fields in Group Drafting a document and inserting fields in the tab Newsletters.

A dialog box will open Selection of fields.

The elements of the address and greeting are listed on the left. The column headings from the data file are listed on the right.

Word searches for the appropriate column for each item. The illustration shows that Word automatically matches the column Surname data file field Surname... But he could not oppose other elements to 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 select the column of the data file corresponding to the item on the left. Now the illustration shows that the column Name matches the field Name... There is nothing wrong with the fact that for the fields Appeal, Unique identificator and middle name no match was found. A compound document does not need to use every field. If you add a field for which there is no data in the data file, it appears in the master document as an empty text placeholder — usually an empty line or a hidden field.

Entering content and adding fields

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

Use a group Drafting a document and inserting fields in the tab Newsletters.

Add any of the following items.

Address block with name, postal address and other information

Select item Address block.

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

Selection of fieldsthen Word was unable to find some of the information it needed for the address block. Click the arrow next to (no match)

Greeting line

Select item Greeting line.

Choose a greeting string format that includes the title, name, and punctuation following the name.

Select the greeting line text for cases where the recipient's first 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 Selection of fields, then Word was unable to find some of the information it needed for the welcome string. Click the arrow next to (no match), and then highlight the appropriate field in the data source required for the merge.

Separate fields

Information can be inserted from individual fields, such as last name, phone number, or sponsorship 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 the field name.

To use more options when inserting individual fields into your document, follow these steps.

In the tab post office 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 highlight the 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 column in the data file, select Database fields.

In the list Fields click the required field.

Click the button Paste, and then Close.

If a dialog box appears Selection of fieldsthen Word was unable to find some of the information it needs to insert the field. Click the arrow next to (no match), and then highlight the appropriate field in the data source required for the merge.

• Custom fields from your Outlook contact list

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

Formatting merge results

To format the data contained in the document, select the mail merge field and format it just like any other text is formatted. The selection must include double chevron quotes ( « » ) surrounding the field.

Merge preview and completion

After all the fields have been added to the main document, you can preview the preliminary results of the merge. Before actually completing the merge, you can review the master documents and, if necessary, make the appropriate changes.

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

Click View Results.

Browse each compound document page by page using the buttons Next post and Previous post in Group Viewing Results.

View a specific document using the command Find a recipient.

Completing the merge

Composite documents can be printed or modified individually. This can be done for all documents or for a subset of documents.

Choose whether to edit the entire set of documents, only the copy currently displayed, or a subset of documents identified by the specified record number. Word saves copies that you want to edit as a single file, where they are separated from each other using page breaks.

Saving the main document

Remember that compound documents are saved separately from the main one. It is convenient to keep the main document separate because it can be useful for subsequent merges.

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.

Merge data sources

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

Other database files

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

With the 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

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

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

Figure: 8.6. Window Select recipients master Merge

2. Click on the command Create a 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 most common fields are pre-listed in the data source creation window. You can remove unnecessary fields from the database and add your own.

Figure: 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 Setting up... The dialog box shown in Fig. 8.8.

4. Highlight the item Appeal.

5. By clicking the button Delete erase the highlighted field.

Figure: 8.8. Setting up data source fields

Figure: 8.9. Dialogue 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, which currently has 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 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 Closeto 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 Edit... The data source form will open. To navigate through the source records, use the buttons The first, Back to, Forward, The last at the bottom of the form.

Figure: 8.10. Toolbar Merge