How to specify Excel sheet name as mail merge source

[UnclePaul]

I want to use one sheet of a multi-sheet Excel 2000 workbook as the data
source for a Word 2000 mail-merge. How / where do I specify the name of the
worksheet that contains the data that is to be merged into the Word document?

 

[Doug Robbins]

I don't remember if in Word 2000, there is a "Confirm conversions on open"
item under Tools>Options>General. If there is, and it has the same effect
as in later versions of Word, when the box next to is checked, when you
attach a mail merge data source, you will be presented with a number of
options for the way in which the data source is connected and with one of
those, you should be able to select the worksheet that you want to use.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[UnclePaul]

Thanks Doug, I found the 'Confirm conversions on open' box and checked it,
but the behavior did not change. Details: In the 'Mail Merge Helper' dialog,
the second step or button 'Get Data' leads to some options, where I choose
'Open Data Source' and navigate to the multi-sheet workbook and Open it. A
dialog box appears, titled simply 'Microsoft Excel', prompting for 'Named or
Cell Range', with a drop-down list. The only item in the list is 'Entire
Spreadsheet'. I suspect that at this point I could specify the sheet to be
used (by typing over 'Entire Spreadsheet' with my selection), but I don't
know the syntax for specifying the sheet name. I think it may be the sheet
name either preceeded by, followed by, or surrounded by exclamation points; I
have tried several combinations but it always insists on using the data from
the first sheet (the one represented by the leftmost tab) of the workbook.

 

[Peter Jamieson]

In Word 2000 the "Confirm conversions on open" option does not normally
affect mail merge data sources. Instead, there is a "Select Method" checkbox
in the Open Data Source dialog box which does more or less the same thing.
If you seleselect it, you should see three possible ways to connect to an
Excel data source: DDE, Converter, and ODBC. In Word 2000, DDE is the
default method. It opens Excel (if neecssary) and usually gets the data more
or less as you see it in the Excel display. But it can only see the first
sheet in the Excel workbook. ODBC is probably the better bet. With ODBC, you
will need to check the Options button in the next dialog box and check all
the options (Tables, views, system etc.) to see all the things you can
connect to, and you may have more trouble formatting your data (e.g. blanks
in Excel numeric columns appearing as zeroes in Word, date fields need
formatting switches in Word MERGEFIELD fields) than with DDE.

Peter Jamieson

 

[Peter Jamieson]

Can you use the filtering options (Mail Merge Helper|Query Options) to
eliminate your blank records?

(If you can set up the merge data source using VBA and OpenDataSource it may
be slightly easier to make this work).

Peter Jamieson