Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
WD97: How to Use MS Query to Merge Two Excel Files for a Mail Merge Data Source
Article ID: 180599 - View products that this article applies to.
This article was previously published under Q180599
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 Microsoft Word Help, type the following text
mail merge, data sourcesand then double-click the selected text to go to the "mail merge data sources" topic. If you are unable to find the information you need, ask the Office Assistant.
The following is an example of merging two Excel files:One Excel file (workbook) could contain a list of ID numbers, first names, last names, and positions, while another Excel file (workbook) 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.
A1: IDNumber B1: Firstname C1: Lastname D1: Position A2: 1 B2: James C2: Jones D2: Clerk A3: 2 B3: Harry C3: Anderson D3: Administration
The IDNumber column in File1 should be similar to the IDNumber column in File2. That is, the column heading should be the same and the information in File1 should correspond to the information in File2. That is, IDNumber 1 should represent the same employee in both sheets. That is James Jones (IDNumber 1 in File1) should live at 333 3rd Ave (IDNumber 1 in File 2). When you connect the two files using Microsoft Query, you can retrieve all of the information about James Jones into a single data source.
A1: IDNumber B1: Address C1: Salary A2: 1 B2: 333 3rd Ave. C2: $20,000 A3: 2 B3: 444 4th St. C3: $25,000
Modifying the Files in ExcelAfter the files have been created, name the ranges of information in each sheet. To do this, follow these steps:
Starting the Mail MergeTo start the mail merge, follow these steps:
Creating a Microsoft Query Data SourceTo create the Microsoft Query Data Source, follow these steps:
Creating the QueryTo create the query, follow these steps:
Adding the Second File and Joining the Data in Microsoft QueryTo join the data in Microsoft Query, follow these steps:
Selecting the Fields You Want to Use in the Mail MergeTo select the fields from the second table you want to use in the mail merge, follow these steps:
Creating the Mail Merge Main DocumentTo create the mail merge main document, follow these steps:
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/155179/ )WD: How to Use a Microsoft Excel Data Source for Word Mail Merge
(http://support.microsoft.com/kb/159817/EN-US/ )WD97: Can't Connect, Insert Database with ODBC and MSQuery
(http://support.microsoft.com/kb/141922/EN-US/ )WD: How to Start a Mail Merge
Article ID: 180599 - Last Review: January 22, 2007 - Revision: 1.2