1. Which version of Word are you using?

2. Word has several different ways to open an Excel file for use as a data
source. Word 2002 and later will typically /try/ to open the data source
using something called OLE DB. If that fails (and I do not know why it is
failing in this case),
a. you /might/ see a dialog box titled Data Link Properties
b. Word will try to open the workbook using an older method called ODBC,
which displays a dialog box like the one you describe.
c. in that ODBC dialog box, the filename presented in the "workbook"
dropdown is not necessarily the one you chose in the "Select Data Source"
dialog box. Unfortunately, if the full path name of the file is long, there
is no way to see it in the dropdown so it is difficult to be sure that you
have selected the correct file. The only way I know to be sure is to ensure
that the part of the file's path name that you /can/ see is enough to
identify the individual file.
d. You may not see /any/ worksheets in the list of tables until you click
the Options button and select all four option checkboxes, then press OK.

That should at least allow you to get connected. If not, and the worksheet
or range you want to connect to is the first one in the workbook, you can
check Word Tools|Options|General|Confirm conversions at open, go through the
connection process again, and try the DDE option when it is offered. This is
an even older connection method that has other limitations (e.g. it cannto
read "Unicode" characters that are non-ANSI) but again, it may be enough to
get you going again.

On the whole it would probably be better to try to ensure that these
workbooks open with OLE DB, but as I say I do not know why OLE DB connection
sometimes fails. Some possibilities:
a. If the workbook you're trying to open has a very long path name (e.g. it
is deep in the folder hierarchy and/or has a long file name) you might
encounter problems.
b. sometimes I have found that using Excel Data List|Create List seems to
cause the problem, or Excel Data|Import External Data, but it doesn't
/always/ seem to cause the problem.
c. it's possible that a worksheet that has different numbers of cells
containing data on each row can cause this problem.

Peter Jamieson