ID: Q180599
The information in this article applies to:
The information in this article describes how to use Microsoft Query to merge two Excel 97 files. You may want to do this so that you can combine related information into a single file and use it in a mail merge.
Merging two Excel files allows you to combine related information into a single file.
Note that there are a number of other ways to create a mail merge data source. For more information about mail merge data sources, click Contents And Index on the Help menu, click the Index tab in Word Help, type the following text
mail merge, data sources
One file could contain a list of ID numbers, first names, last names, and positions, while another file could contain a list of ID numbers, addresses, and salaries.
If the ID numbers in both files are the same, you can link the two files by using the similar ID field. This allows you to create a mail merge that contains contain the ID numbers, first names, last names, addresses, positions, and salaries. To put the files together inside Microsoft Query they need to be in the correct format in Excel. Each column of information needs to have a heading name (do not use spaces, quotation marks, apostrophes, or hyphens in the heading name).
Here are two sample Excel files. In File1, cell A1 contains the word IDNumber, cell B1 contains the word Firstname, cell C1 is Lastname, and cell D1 is Position. In File2, cell A1 contains the word IDNumber, cell B1 is Address, and cell C1 is Salary.
File1:
A1: IDNumber B1: Firstname C1: Lastname D1: Position
A2: 1 B2: James C2: Jones D2: Clerk
A3: 2 B3: Harry C3: Anderson D3: Administration
A1: IDNumber B1: Address C1: Salary
A2: 1 B2: 333 3rd Ave. C2: $20,000
A3: 2 B3: 444 4th St. C3: $25,000
After the files have been created, name the ranges of information in each sheet. To do this, follow these steps:
1. Start Microsoft Excel.
2. Open the first workbook and select the sheet that contains the
information you want to use in the mail merge. Select the portion of
the file containing the text.
and click OK.
5. Repeat steps 1-4 for the second file. (Be sure to save the files in the
same directory.)
After modifying the files in Excel, you can start the mail merge in Word.
To start the mail merge, follow these steps:
1. Open Word.
2. On the File menu, click New, and then click OK.
3. On the Tools menu, click Mail Merge.
4. Click Create, click Form Letters, and click Active Window.
Now you're ready to create the Data Source.
To create the Microsoft Query Data Source, follow these steps:
1. In the Word Mail Merge Helper, click Get Data, and click Create Data
Source. Click the MS Query button.
Wizard to create/edit queries" check box, and click OK.
4. Under Select A Driver, select Microsoft Excel Driver (*.xls).
5. Click Connect.
6. Select the Database Version (for example, Excel 97), and click Select
Workbook.
Choose Data Source dialog box.
To create the query, follow these steps:
1. Your newly created data source should be highlighted in the Choose Data
Source dialog box. If not, select it, and then click OK.
Tables And Columns, double-click the named range that appears.
This will reveal your column titles.
mail merge data source and click the ">" button (note that you must
select a field that both sheets have in common--in this case IDNumber).
5. Select the "View Data Or Edit Query In Microsoft Query" option and
click Finish.
To join the data in Microsoft Query, follow these steps:
1. In Microsoft Query, click Add Tables on the Table menu.
2. At the bottom of the Add Tables dialog box, next to Workbook, select
the other Excel file you want to use in the merge. (Note that this file
must be located in the same directory as the file you added in the
Wizard.)
4. In the first Excel table, click the field you want to join (for
example, the IDNumber field) and drag the pointer to the similar field
in the second Excel table.
A join line should appear.
NOTE: Some field names may appear in the query pane (the big white
rectangle under the tables). You can use these fields, or you can
delete each one by positioning the insertion point over the field name
until a down arrow is displayed. To delete the field, click to select
the column, and press DELETE.
To select the fields you want to use in the mail merge, follow these steps:
1. In the first table, click the first field name that you want to use.
2. Drag the field name to the query pane.
3. Repeat steps 1-2 for each field that you want to use.
4. On the File menu, click Return Data To Microsoft Word.
Word for Windows will then prompt you to Edit Your Main Document.
To create the mail merge main document, follow these steps:
1. When you receive the "Word found no merge fields in your main
document..." message, click Edit Your Main Document.
The merge fields from both Excel files are now available on the Insert
Merge Field button on your mail merge toolbar.
Field on the mail merge toolbar.
4. Click the Merge button.
5. Click the Merge button to merge to a new document.
The merged data should include information from both Excel tables.
For additional information, please see the following articles in the Microsoft Knowledge Base:
ARTICLE-ID: Q142756
TITLE : WD: How to Design and Set Up Mail Merge Data Sources
ARTICLE-ID: Q155179
TITLE : WD: How to Use a Microsoft Excel Data Source for Word Mail
Merge
ARTICLE-ID: Q159817
TITLE : WD97: Can't Connect, Insert Database with ODBC and MSQuery
ARTICLE-ID: Q141922
TITLE : WD: How to Start a Mail Merge
Keywords : kbinterop kbmerge
Version : WINDOWS:8.0,97
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: February 12, 1999