How to specify Excel sheet name as mail merge source

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

UnclePaul - 21 Jan 2005 22:41 GMT

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?

Reply to this Message

Doug Robbins - 21 Jan 2005 23:56 GMT

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.

Signature

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

>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?

Reply to this Message

UnclePaul - 22 Jan 2005 01:35 GMT

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.

> 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
[quoted text clipped - 8 lines]
> > worksheet that contains the data that is to be merged into the Word
> > document?

Reply to this Message

Peter Jamieson - 25 Jan 2005 07:49 GMT

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

> 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'
[quoted text clipped - 30 lines]
>> > worksheet that contains the data that is to be merged into the Word
>> > document?

Reply to this Message

Diana Jordan - 04 May 2005 16:28 GMT

I am trying to set up some mergeable documents that will help non-users be
able to run a mail merge in Word 2k with a minimum of effort (by clicking the
merge button).

I have tried to use the suggestion below to run a mail merge off the second
sheet of an Excel spreadsheet I have pre-formatted for data entry and keep
getting an "unable to connect" error. If I used the name of the named range
on the sheet using ODBC, it transferrs all blank lines below the data as the
named range is columns A:I (allowing for new entries by users).

Is there a way to suppress the blank lines so the named range does not need
to be reset every time a merge is run?

> In Word 2000 the "Confirm conversions on open" option does not normally
> affect mail merge data sources. Instead, there is a "Select Method" checkbox
[quoted text clipped - 11 lines]
>
> Peter Jamieson

Reply to this Message

Peter Jamieson - 05 May 2005 08:49 GMT

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

>I am trying to set up some mergeable documents that will help non-users be
> able to run a mail merge in Word 2k with a minimum of effort (by clicking
[quoted text clipped - 35 lines]
>>
>> Peter Jamieson

Reply to this Message