Word (2002) cannot see Excel (2002) source data

[Matilda]

My source data is in a multisheet Excel file. I have selected and named the
range for the source data in the Excel file. The Word document can see the
spreadsheet, but not the range. It can see ranges in other spreadsheets in
the same location, but not my target file. Is it just me?

 

[Peter Jamieson]

Is the spreadsheet open in Excel or closed?

My best guess is that your workbook may be corrupted in some way. Can it be
reasonably easily recreated from scratch?

After you select the file in Select Data Source, you see a further dialog
box. Is it
a. a box with title "Select Table" and a list with Name, Description,
Modified, Created, Type or
b. a box with title "Select Table", a list box titled "Table", an "Options"
button etc. or
c. a box with title "Microsoft Excel" a text field titled "Named or Cell
Range" and initially containing the text "Entire Spreadsheet" ?

In Word 2002 I would expect that you see (a), but I would like to be sure.
If it is (a), are all the sheet names in the list?

If it is (b), then
- it sounds to me as if Word has failed to connect using OLEDB (which is
what it is attempting in (a)) - unless you have deliberately selected the
ODBC option (and you can't do that accidentally).
- for now, you could click the Options button and select all the options in
there. You may also need to check that the pathname in the Workbook field in
this dialog actually contains the name of the file you are trying to open.
This can be difficult to see because most pathnames are far too long for the
box - you could try copying your file to a path with a shorter name just to
check.

if it is (c) - and it should not be unless you deliberately chose the DDE
connection method - then you will only be able to connect to named ranges
in the first sheet of the workbook.

Best I can do for now.

Peter Jamieson

 

[Matilda]

Is the spreadsheet open in Excel or closed?

Tried both

My best guess is that your workbook may be corrupted in some way. Can it be
reasonably easily recreated from scratch?

No, unfortunately - 8 sheets and 4MB

After you select the file in Select Data Source, you see a further dialog
box. Is it
a. a box with title "Select Table" and a list with Name, Description,
Modified, Created, Type or
b. a box with title "Select Table", a list box titled "Table", an "Options"
button etc. or
c. a box with title "Microsoft Excel" a text field titled "Named or Cell
Range" and initially containing the text "Entire Spreadsheet" ?

Expect a) but get b), without any ranges listed under Table. Clicking on
another workbook in the drop down box shows their ranges, however.

In Word 2002 I would expect that you see (a), but I would like to be sure.
If it is (a), are all the sheet names in the list?

As above .... in some workbook instances

If it is (b), then
- it sounds to me as if Word has failed to connect using OLEDB (which is
what it is attempting in (a)) - unless you have deliberately selected the
ODBC option (and you can't do that accidentally).
- for now, you could click the Options button and select all the options in
there. You may also need to check that the pathname in the Workbook field in
this dialog actually contains the name of the file you are trying to open.
This can be difficult to see because most pathnames are far too long for the
box - you could try copying your file to a path with a shorter name just to
check.

if it is (c) - and it should not be unless you deliberately chose the DDE
connection method - then you will only be able to connect to named ranges
in the first sheet of the workbook.

Best I can do for now.

Thanks, Peter. Funny thing but doing all these things, and throwing in the
Tools/Options/Calculation/Update Remote References and following prompts
(phew) then restarting again and trying a link again in Word and...... banzai
!!!

Wish I knew which was the magic stroke.

( Meant to click that this post was helpful - it was - did I? )

 

[Peter Jamieson]

Glad you got it sorted.

If you're still following the thread, just out of interest, are you now
seeing dialog (a)?

( Meant to click that this post was helpful - it was - did I? )

I don't know - I don't use the web interface to these groups so I don't see
the same stuff as you do. But thanks anyway!

Peter Jamieson