Selecting a specific range or cell to merge from an excel data source

[Stephanie]

When I try to choose a specific range or cell to merge
from my excel data source onto my labels template, I get
an error message:Word could not establish a DDE connection
to microsoft excel to complete the current task. However I
am able to merge if I choose the entire spreadsheet. What
does this error mean, and how can a choose only one range
of cells?

 

[Cindy M -WordMVP-]

Hi Stephanie,

When I try to choose a specific range or cell to merge
from my excel data source onto my labels template, I get
an error message:Word could not establish a DDE connection
to microsoft excel to complete the current task. However I
am able to merge if I choose the entire spreadsheet. What
does this error mean, and how can a choose only one range
of cells?

You can't, with DDE. It only supports the entire FIRST sheet.
(Back in the days when DDE was "state-of-art" Excel only
provided one sheet...)

Which version of Word do you have? Have you tried ODBC or
Word's internal spreadsheet converter?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Stephanie]

I have Windows XP. How would I go about trying ODBC or
Word's internal spreadsheet converter?

 

[Cindy M -WordMVP-]

Hi Stephanie,

I have Windows XP. How would I go about trying ODBC or
Word's internal spreadsheet converter?

I assume you mean WORD 2002; the version of Windows is
basically irrelevant...

Activate "Confirm conversions on open" in
Tools/Options/General, and you'll get a list of connection
methods after you've chosen the data file.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Stephanie]

I actually have Word 2000. I don't know if that makes a
difference, but I did follow the instructions below and
selected "Confirm coversions at open," but I never got a
list of connection methods after I opened my data file.

 

[Peter Jamieson]

In Word 2000 you need to check the "Select method" box in the Open Data
Source dialog box.

 

[Stephanie]

There is not dialog box when I open the data source.

 

[Stephanie]

Actually, I'm using our agency management system as the
data source, then I transferred the info an excel
worksheet. Does that make a difference. Is there some kind
of step by step instruction for this, because I'm really
having a hard time figuring this out.

 

[Peter Jamieson]

It sounds to me as if you are bypassing the normal way(s) to set up a
mailmerge. In Word 2000, once the document is set up as a mail merge
document, you typically see the mail merge toolbar, which contains a button
for the Mail Merge Helper, which contains a button that lets you open the
data source.

If you don't see that, it might help if you can spell out what you actually
do after this:

 

[Stephanie]

Yes, I do have the mail merge toolbar in my document, so I
click on the mail merge helper, then get data, then open
data source, then choose my data source, then an excel box
pops up and it says "Named cell or range:" I can
choose "Entire Spreadsheet" or input only a certain range
of cells I want to merge. When I choose a certain range, I
get a word warning message "Word could not establish a DDE
connection to MS Excel to complete the current task." If I
leave it "Entire Spreadsheet," all records merge ok, but I
want to choose only certain records from the excel data
source.

 

[Cindy M -WordMVP-]

Hi Stephanie,

Peter's away for a while, so I hope you don't mind my taking
up the thread, here, again.

Despite what the dialog box says, DDE only lets you connect
to the first sheet in an Excel workbook. DDE dates from
before Excel contained more than one sheet to a file
(multiple sheets was only introduced in 1993, or so, with
Excel 5.0).

If you want to use a different sheet, or another range, then
you should try an ODBC connection. Activate the "Select
method" checkbox in the "Open Data source" dialog box. After
you choose your Excel sheet and click "Open" (or OK, I forget
how the button is labelled), you should get a list of
connection methods; DDE will probably be preselected. Try
ODBC, instead.

If you have a named range, this should appear in the list of
tables. In order to choose a particular work sheet, click
"Options" and activate the checkboxes in the dialog box that
appears.

Yes, I do have the mail merge toolbar in my document, so I
click on the mail merge helper, then get data, then open
data source, then choose my data source, then an excel box
pops up and it says "Named cell or range:" I can
choose "Entire Spreadsheet" or input only a certain range
of cells I want to merge. When I choose a certain range, I
get a word warning message "Word could not establish a DDE
connection to MS Excel to complete the current task." If I
leave it "Entire Spreadsheet," all records merge ok, but I
want to choose only certain records from the excel data
source.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Stephanie]

O.K., I finally figured out where the "select method" box
was, and I checked it, found my data source, chose the
ODBC connection method, then the "select table" box came
up, and I chose options, activated all checkboxes, clicked
OK, then it took me back to my merge.doc. So, from here
I've tried several methods to merge a single record, such
as "find record" so I'll choose a record # (which I'm
assuming would be the number of the row in excel), and the
computer will search, and nothing happens. I also tried
using the mail merge helper box, click "merge" and where
it says "records to be merged", I'll select something
like "From: 856 To: 857" and it will merge records 857-
886. I can't seem to figure this out.

-----Original Message-----

 

[Stephanie]

I figured out how to get one label, I just deleted the
merge fields in the rest of the document. Now I have
another issue...I want to be able to save this document in
our shared drive so that all of our employees can go into
it throughout the day, and insert a label they might need
from the data source, that way we can just print one set
of labels per day. However, when I merge this one label,
it's no longer a merge doc. It looks like I would have to
merge everthing I wanted all at once. Is this true?

 

[Cindy M -WordMVP-]

Hi Stephanie,

I'm not certain I understand the question. You mean you want
to accumulate single labels throughout the day, then print
them all in one go?

In this case, you need to modify your approach. Add a new
field to the Excel data source. Have the employees open the
Excel sheet and type an "x" (or something similar) into that
field if they need a label.

At the end of the day, run the merge to merge only those
records marked with an "x". In order to set this filter,
click on the "Mail merge..." button in the Mail Merge
toolbar, then click on "Query Options". Select the field name
in the first column, then "Is Not Blank" from the list in the
second column. OK. (You should only need to do this the one
time and it would be retained in the saved main merge
document).

After the merge has been performed, you'd need to open the
Excel file and delete the "x" from that column.

This also means that you'll need to recreate the label sheet
so that you have label entries in all the cells on the sheet.

I figured out how to get one label, I just deleted the
merge fields in the rest of the document. Now I have
another issue...I want to be able to save this document in
our shared drive so that all of our employees can go into
it throughout the day, and insert a label they might need
from the data source, that way we can just print one set
of labels per day. However, when I merge this one label,
it's no longer a merge doc. It looks like I would have to
merge everthing I wanted all at once. Is this true?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Stephanie]

O.K., first of all, thanks for all the time you've spend
on this problem with me. Now here's my next road block.
When I select query options and the "select workbook"
dialog box appears, and I click O.K. to select the
workbook, I get a message that says "ODBC Excel driver
login failed. Unrecognized database format." There is also
a box that comes up when I select query options that
says "connecting to data source." If I don't click O.K. in
the "select workbook" dialog box, and just wait for the
connection, it never connects, I just keep getting a
message that says "The task is taking longer than
expected. Do you want to continue waiting?" I can click
yes all day, but nothing happens, and when I click no, I
get the message "Word could not establish a DDE connection
to Microsoft Query to complete the current task." So, I'm
stuck once again.

 

[Stephanie]

Nevermind that last reply, I actually figured it out with
your help. I can't believe it! Thank you very much.

 

[Cindy M -WordMVP-]

Hi Stephanie,

This is a misunderstanding. Do NOT try to use MS Query. The
query options to which I refer are not set there. When you
select "Query options" Word is asking whether you want to use
MS Query? Say "No". You should then get Word's internal
"Query options" dialog box.

Note that the merge document needs to have been connected to
the Excel worksheet as a data source, prior to this. I.e. you
should have merge fields in the main merge document and be
able to view various records with <<ABC>> activated. If you
can't view any records, then you aren't connected to the data
source and have to do this, first.

O.K., first of all, thanks for all the time you've spend
on this problem with me. Now here's my next road block.
When I select query options and the "select workbook"
dialog box appears, and I click O.K. to select the
workbook, I get a message that says "ODBC Excel driver
login failed. Unrecognized database format." There is also
a box that comes up when I select query options that
says "connecting to data source." If I don't click O.K. in
the "select workbook" dialog box, and just wait for the
connection, it never connects, I just keep getting a
message that says "The task is taking longer than
expected. Do you want to continue waiting?" I can click
yes all day, but nothing happens, and when I click no, I
get the message "Word could not establish a DDE connection
to Microsoft Query to complete the current task." So, I'm
stuck once again.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Cindy M -WordMVP-]

Hi Stephanie,

I actually figured it out

Great Glad you persevered through the maze!

Cindy Meister