How to specify Excel sheet name as mail merge source

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?

I don't remember if in Word 2000, there is a "Confirm conversions on open"
item under ToolsOptionsGeneral. 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" wrote in message
...
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?

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.

"Doug Robbins" wrote:

I don't remember if in Word 2000, there is a "Confirm conversions on open"
item under ToolsOptionsGeneral. 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" wrote in message
...
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?

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


"UnclePaul" wrote in message
...
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.

"Doug Robbins" wrote:

I don't remember if in Word 2000, there is a "Confirm conversions on
open"
item under ToolsOptionsGeneral. 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" wrote in message
...
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?

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?

"Peter Jamieson" wrote:

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

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
"Diana Jordan" wrote in message
...
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?

"Peter Jamieson" wrote:

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