Excel Range Name to apply in word when merging

[Darlene]

I have many documents that I merg with excel. It comes up with drop down
menu of worksheets in a workbook.

The next drop down asks for Name or Cell Range. The only one that you can
use is entire worksheet.

I have named a range in my workbook that I use to merge but I can not get it
to drop down here. I always have to type in R1C1:r450c29 for it to bring up
just the information I want.

Any suggestions would be greatly appreciated.

 

[Doug Robbins]

From the Tools menu, select Options and then on the General tab, check the
box against the confirm conversions at open item. After doing that, when
you attache the date source to the document, you will be offered a number of
different options for the method of connection. Try the DDE option and you
should be able to do what you want.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Darlene]

Doug

I can not access DDC and I went into tools and it was already checked off

 

[Peter Jamieson]

If you are already using DDE as Doug Robbins has suggested you should, named
ranges are only dispayed in the list if they are in the first sheet in the
workbook. Otherwise, you have to type the range into the dialog box using
RaCa:RzCz format as I think you have already discovered.

Peter Jamieson

 

[Darlene]

Peter:

This is the first worksheet in the workbook but I cannot get a dde
connection my computer I have to use the other one that comes up.

 

[Peter Jamieson]

Can you describe what you see? For one thing, what does happen when you try
to make a DDE connection?

Peter Jamieson

 

[Darlene]

Peter:

It pops up say a dde connection can not be established

 

[Peter Jamieson]

Which version of Word are you using?

From your description, you are opening your Excel workbook using the
"Microsoft Excel Worksheet via Converter". Despite the text in the dialog
box I do not think the Converter actually lets you select a named range as a
data source. Do you see any options other than that one and "MS Excel
Worksheets via DDE" ? In Word 2000 and earlier, you might also see something
like "Excel Files via ODBC", and in Word 2002/2003 you might also see an
OLEDB option. If you see either of those options, please try it - you should
see the named ranges listed in either case (although they are more likely to
disappear when uisng the ODBC option).

Alternatively, the problem you may have with DDE is that the workbook could
already have been opened. In that case, the DDE option may fail with a
similar message to the one you are seeing. If the workbook is already open
in Excel, there should not be a problem. If however, your Word document, or
another Word document, or a template one of your open documents is attached
to, is already set up with the workbook as the data source, a DDE connection
will fail. In that case you should first disconnect the offending mail merge
main document from the data source - the simplest way is often simply to
connect to a different workbook as a data source, but you can also
disconnect by using Mail Merge Helper (in Word 2000 and earlier - use the
first button in the dialog) or the Mail Merge Wizard or Toolbar (Word 2002
and later - find the relevant task pane in the Wizard or enable the toolbar
using Tools|Customize and use the first button) to turn the mail merge main
document back into a non-mail merge document. Then try making the connection
again.

Peter Jamieson



When you try to make the DDE connection, is the spreadsheet already open?
E.g., does the Word document already have a connection to it? If so, can you
please remove the connection (e.g. in Word 2000 or earlier use the first
button in the Mail Merge Helper to make the document not a Word document; in
Word 2002 or later, use Tools|Customize to enable the Mailmerge toolbar and
use the first button to make the document a "Normal word document". Then try
to make the connection.

If you are using Word 2000 or earlier, the types of connection can be DDE,
ODBC, or "Converter".