It sounds as if what is happening is that Word is successfully opening
the workbook using its default method (OLE DB), but failing to open the
worksheet, then falling back to trying to use ODBC. If you are seeing
the ODBC dialog box you would typically need to
a. verify that the selected "database" is the excel filethat you
expect. That's not always easy because the dialog box does not show long
path names properly
b. click Options and select all four checkboxes.
However, the chances are that if Word can't open using OLE DB, it won't
be able to do it with ODBC either. that may be because...
Your Excel workbook has changed (possibly even been corrupted) in a way
that makes it impossible for the Access/Jet OLE DB provider to get the
data.
Things you might have done to your workbook that could affect this
include stuff such as creating lists/databases/outlines within the
relevant worksheet
If your workbook/worksheet is very simple (e.g. just a rectangular table
of data with no formulas etc.) it is probably worth exporting your data
to a format such as .csv, then recreating your workbook and re-importing
the data.
If none of the above helps, a possible short term workaround is to
copy/paste your Excel data into a new Word document and use that as the
data source for your merge (depending on how many columns your worksheet
has, you may find that the copy/paste creates a word table or a set of
tab-delimited Word paragraphs).
Peter Jamieson
http://tips.pjmsn.me.uk