Problem importing recipients from nominated Excel worksheet

[Blackard]

I'm attempting to do a mail merge on a letter created in Word 2007. When I
chose "Select Recipients", I then selected "Use existing list" then browsed
to locate the correct Excel 2007 file. The correct worksheet was identified.
No problem so far. However, when I selected the worksheet, the "Select table"
screen shows an entirely different file name and location. If I arrow down,
other file names and locations are listed, but not the one I selected. There
is nothing in the table list. Any thoughts on a solution?

 

[Peter Jamieson]

It sounds to me as if you are seeing the ODBC connection dialog box when
you should be seeing the OLE DB dialog box. When Word tries to connect
to an Excel data source, it tries OLE DB first, but if it cannot open
the Excel workbook using OLE DB, it tries ODBC, which is an older
method. (Then if that does not work, it tries DDE, which is even older).

The ODBC dialog box behaves in the way you describe - it does not
necessarily select the file name you specified (in fact, if the path
name is long, you may not be able to see the entire path name at all).
If you can select the correct file, you should be able to see all the
worksheets and named ranges by clicking Options and selecting all four
options.

However, the fact that you are seeing the ODBC dialog at all is a sign
that there could be something wrong with your workbook, are at the very
least, something that OLE DB cannot "understand." For example, if the
workbook is password-protected, OLE DB cannot open it. But ODBC cannot
do so either. In that particular case you would have either to remove
the protection, or use DDE to open the workbook.

If you need to do that, you can either wait until ODBC connection has
failed (in which case you should see the DDE dialog) or
- check Word Office Button->Word Options->Advanced->General->"Confirm
File format conversion on open".
- go through the connection process again. You should see a dialog box
titled "Confirm Data Source". It will probably just show "OLE DB
Database Files".
- click "Show all"
- scroll down the list and select "MS Excel Worksheets via DDE (*.xls)"
- click OK

Excel should open (if it is not already open) and open the workbook (if
necessary), at which point you will see any password dialog.

NB you can only get data from the first worksheet in the workbbook with
DDE, or from named ranges in that sheet. Further, you don't get Unicode
data (same with ODBC).

If you do not know a reason why your workbook would not open with OLEDB,
you could consider reconstructing it. Although it seems that working
with lists/databases within Excel (aplying filters/grouping) can
sometimes cause this kind of problem, I have never got to the bottom of
what causes problems for OLE DB.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Peter Jamieson]

- go through the connection process again. You should see a dialog box
titled "Confirm Data Source". It will probably just show "OLE DB
Database Files".

NB, if it's a .xsls or .xlsm, you just see OLE DB Database files. If
it's a .xls, you should see the OLE DB, ODBC and DDE options.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Peter Jamieson]

I realise now that if it's a .xslx/.xlsm, you really do see the wrong
tables in the ODBC dialog box even when you have selected the correct
file. And that occurs even if you explicitly select the ODBC option.

So I guess it's either "fix the .xlsx/.xlsm if possible," or use DDE.

Sorry about that - another nail in the coffin of Word MailMerge usability.

Peter Jamieson

http://tips.pjmsn.me.uk