Mail merge using Excel as data source

Topic

New Reply

I want to be able to specify a particular worksheet within an Excel workbook to use as the data source for a mail merge. For example, in MYDATA.XLS I might want to merge the data from Sheet3 today and the data from Sheet5 next week. I have not been able to find any reference to this in the help and there doesn’t seem to be any options available when choosing the data source to be able to specify the worksheet, only the name of the workbook.

Reply | Quote

Replies

Hi Ian:

Here are some tips that I copied some time ago.

MailMerge: Merging from Excel Worksheets
(Source: Allen Wyatt’s WordTips Gold & KB Q116470 & Q212314)
When using an Excel worksheet as a datasource, you may have noticed that you can only access information in the first worksheet of an Excel workbook. This is a recognized flaw in Excel.

There are two ways you can access Excel information: using DDE (Dynamic Data Exchange) format or using ODBC (Open Database Connectivity). The problem affects Word 6, 7, 97 & 2000.

Method 1: Using DDE Directly
If you use DDE, which is the default method, you must make sure that the information you want to use is on the first sheet in a workbook. Additionally, if you are accessing only a named range, then you will want to make sure that the sheet containing the range is the first sheet in the workbook. If you need to move worksheets, make sure you do so before you link to the data from Word. If you try moving the sheets after linking, then Word will not recognize your changes.

1. In the Get Data list, click Open Data Source.
2. Locate and select the Microsoft Excel file. Make sure that the Select Method box in the Open Data Source dialog box is selected.
3. Click Open.
4. Select MS Excel Worksheets via DDE in the Confirm Data Source dialog box. Click OK.
5. In the Microsoft Excel dialog box, select the Entire Spreadsheet or Named or Cell Range from the list. Click OK.
6. Click Edit Main Document and continue with the merge process.

Method 2: Using the Data Converter
1. Click on Tools/MailMerge/Create.
2. Select any type of document.
3. Click Get Data/Open DataSource/ & select the Excel file you want to use.
4. Make sure the Select Method check box is selected (Word 7, 97, & 2000), at the right side of the dialog box(bottom center in Word 2000). (In Word 6, you should select the Confirm Conversions check box instead.)
5. Click on Open. This displays the Confirm Data Source dialog box.
6. Select the Microsoft Excel Worksheet via Converter (*.xls, *.xlw) option.
7. Click on OK. This displays the Open Worksheet dialog box. (In Word 6, it is the Open Workbook dialog box.)
8. Using the controls in the dialog box, select the portion of the Excel workbook you want to use as your mail merge data. (If you are using Word 6, click on OK this displays the Open Worksheet dialog box. Select the portion of the worksheet you want to use.)
9. Make sure the Format for Mail Merge check box is selected.
10. Continue setting up your mail merge documents as you normally would.

Method 3: Using ODBC
This works in Word 7, 97, or 2000. Before you can use this method, however, you need to make sure that ODBC is installed on your system. You can check this out by simply opening the Control Panel and seeing if there is an ODBC applet there. If there is not, then you must install ODBC (using the Windows Setup program) before this method can work.

1. Using Excel, define and name a range that identifies the data you want to use in the merge operation. Make sure you include the column headings in the range definition. Also, your range name should contain no spaces. Save and close your Excel workbook.
2. Start Word.
3. Click on Tools/Mail Merge/Create to set up your document. You can select any type of document you want to use in this mail merge operation.
4. Click on Get Data/Open Data Source. This displays the Open Data Source dialog box.
5. Select the Excel file you want to use as your data source (the one you closed in Step 1) but make sure the Select Method check box is selected, at the right side of the dialog box(lower center in Word 2000). (In Word 6, you should select the Confirm Conversions check box instead.)
6. Click on Open (displays the Confirm Data Source dialog box) & select the Excel Files via ODBC (*.xls) option. Then click on OK.
7. In the Select Table dialog box, select the data range you defined in Step 1. In Word 2000, only the first worksheet in the workbook will show, or click Options and select System Tables, and specify the worksheet you want to use. Click OK.
8. Continue setting up your mail merge documents as you normally would.

In summary, when merging from Excel, you have two possibilities in Office 2000.

1. Tool/Mail Merge
2. Get Data
3. Select the file; activate “Select Method”
4. Choose the method to link to the data source:
a. DDE
b. Word’s internal spreadsheet converter

The internal converter should offer you a list of worksheets in the workbook from which you can choose; DDE will let you select any NAMED RANGE in the workbook, no matter what sheet it’s on. The internal converter will probably be faster than DDE.

Hope this helps.

Reply | Quote

Wow, this is great! I ask a question, go to bed (down-under in New Zealand) and wake up to find multiple ways to skin my troublesome cat!!
Thank you Phil for your comprehensive reply – I am somewhat embarassed to see that there was a reference to the MS knowledge base. I had a look there some time back but couldn’t uncover anything that seemed to help – perhaps I need to hold my screen upside down to counter the antipodean effect
Thanks again

Reply | Quote