mail merges and excel data fields

[rebecca]

Hi,

I'm having a problem when merging word documents with an excel data source.

Basically what's happening is:
With my excel document set up: with headings along the first row and mailing
details etc in the rest of the rows. I set the print area, save and close
it.
I then open my merge letter (word document) I follow the steps to link the
excel document to the word document using the mail merge toolbar. After
selecting the data file the MS Word then asks me which table I'd like to use?
I select the print area version of the document I want but instead of going
back to the word document so I can insert my merge fields, it gives me a
second table with nothing in the list and a workbook address of where the
data source is saved. It gives me options of clicking Ok or cancel; if I
click Ok a dialogue then pops up saying word was unable to open the data
source.

It only seems to be intermittant so I'm really not sure of why it happens
with some data fields and not with others. If someone knows the answer I'd
really appreaciate hearing from you.

Rebecca

 

[Andrea Jones]

What version of Office are you using?

Andrea Jones

 

[Peter Jamieson]

Word has several different methods it can use to open an Excel data source -
without going too deeply into it, Word 2002 and later
a. try to open Excel using OLEDB (that's the first dialog you see).
b. if that fails, Word tries to open Excel using ODBC (that's probably the
second dialog you see. In this case, you typically don't see any of the
sheets listed unless you click Options and check all the boxes in there.
Even then, the workbook listed in the dialog may not actually be the one you
specified, and you may or may not be able to see that because the full path
name may be too long to display).
c. if that fails, Word should try to open using DDE, with another dialog.
If your print area is in the first worksheet in the workbook you should be
able to select it. If not, you won't be able to use this method. DDE opens
Excel as necessary.

You can select these methods explicitly by first checking Word
Tools|Options|General|"Confirm conversion at open". When you try to connect,
you will eventually see an extra dialog box that lets you choose the
conneciton method. If DDE works for you, use it. But be aware that if you
are using Unicode characters (e.g. some types of accented character) in your
data, they won't get through with DDE or ODBC.

What that doesn't tell you, of course, is why exactly (a) does not work in
the first place on some occasions, and I do not know the answer to that. I
think it is either
d. the workbook is "locked" and Word cannot open it (although Word can
usually open it, even if it is already open in Excel). When you have this
problem on your system, it may be worth trying to open the data source
either when it is open in Excel, or when it has been closed, and seeing if
it makes any difference - if it does, please post back (you could also try
saving the workbook before connecting from Word).
e. the workbook or worksheet has been corrupted in some way, in which case
you may have to export the data from your sheet (e.g. to .csv format) then
import it into a completely new workbook.

Peter Jamieson

 

[rebecca]

Hi Andrea, it's MS Word 2003

What version of Office are you using?

Andrea Jones

 

[rebecca]

THANK YOU!!! you are now my Word/Excel god!

It didn't seem to want to work when I tried checking the boxes within the
'Options' but once I check the 'Confirm conversion at open' box, it worked
without a problem.

I have actually tried both of 'reasons' you gave as to why it happens in the
past (ie making sure excel is closed and creating a new doc with the data)
and neither seem to get past the second dialogue box. So still very weird
that it happen sometimes and not others.

Anyway, thank you so much for providing me with a way to get past the
problem.

kind regards
Rebecca

 

[Peter Jamieson]

I have actually tried both of 'reasons' you gave as to why it happens in

the
past (ie making sure excel is closed and creating a new doc with the data)
and neither seem to get past the second dialogue box. So still very weird
that it happen sometimes and not others.

Yes,that's interesting. When I have time I may have a further look at this.
It could be something to do with using the Print Area.

Peter Jamieson