Can't link to Excel2007B2TR Spreadsheet Named Ranges from Word2007

[Erik Bo Sørensen]

Hi after "up"grading to Office2007B2TR links from Word to Named Ranges in
Excel no loger seems to work!

Links to Named Ranges (in casu only cells) in spreadsheets the new way:
• In Spreadsheet: [Ctrl-C] in cell
• In Word:
[Ctrl+Alt+V] (Paste Special)
[Alt+L] (Paste Link)
[Alt-A] (As)
(Unformatted Unicode Text)

Creates this field in Word-document:
{ LINK Excel.Sheet.8 "\\\\Server\\documents\\TEMP\\Book1.xlsx"
"Sheet2!Test2Name" \a \t \u }

Alt-F9 displays the content of the cell:
Test2

Which seem to work OK (a lot of new limitations though: It looks in
Office2007B2TR like the spreadsheet name must not contain space or special
characters and the Name of the range must no contain space …)

But after saving, closing and reopening the Word document IT DOES NOT WORK
ANYMORE.

When Word tries to update the link to the named range, it creates this
rather disappointing messagebox:

Word is unable to create a link to the object you specified.
Please insert the object directly into your file without creating a link.
[OK]

(Dear MS, I chose Link for a reason!)

And instead of the content of the linked cell, Word displays:
Error! Not a valid link.

The field haven’t changed. Alt-F9 displays the Field:
{ LINK Excel.Sheet.8 "\\\\Server\\documents\\TEMP\\Book1.xlsx"
"Sheet2!Test2Name" \a \t \u }

This is rather annoying as I’m using a lot of references to spreadsheets to
keep my documents up-to-date and consistent.
(I’m running Off2k7B2TR en-us on WinXPProfSP1 da-dk)

Anybody have any solutions or ideas?

 

[Erik Bo Sørensen]

Hi all,

Conclusion:
Research indicates that Word2007 (or I - though everything else including
LINK Fields in Office2003 works just fine?) has serious problems on networks

The only “SOLUTION†I've found so far is to
save BOTH Word-document .docx AND Excel workbooks .xlsx to local drive.

Restarting Word and opening the document [Ctrl+O] Word displays the warning
! This document contains links that may refer to other files. Do you want
to update
this document with the data from the linked files?
[Yes] [No]
- Which use to be a good sign (Word is recognizing the LINK Field)
- Updating the LINK’s [Ctrl+A], [F9] works fine.

The Lab:
I created 2 workbooks and a Word document with 4 LINK Fields
a) Link to named range: Test2 – spreadsheet on local drive
* i.e.: { LINK Excel.Sheet.8 "D:\\Temp\\Book1.xlsx" "Sheet2!Test2Name" \a \t
\u }
b) Link to RnCn range: Test3 – spreadsheet on local drive
* i.e.: { LINK Excel.Sheet.8 "D:\\Temp\\Book1.xlsx" "Sheet2!R2C1" \a \t \u }
c) Link to named range: Test4 – spreadsheet on network drive
* i.e.: { LINK Excel.Sheet.8 "\\\\server\\share\\path\\Book2.xlsx"
"Sheet2!Test4Name" \a \t \u }
d) Link to RnCn range: Test5 – spreadsheet on network drive
i.e.: { LINK Excel.Sheet.8 "\\\\server\\share\\path\\Book2.xlsx"
"Sheet2!R4C1" \a \t \u }

Findings:

Saving Word document AND/OR Excel workbook to Network Drive don’t work
I tried:
1) Save Word – Always create backup copy disabled – AND Excel documents to
network drive
2) Save Word – Always create backup copy disabled – document to network
drive AND Save Excel document to local drive.

BUG #1: Erroneous interpreting Named Ranges
Creating the LINKs to the workbook on the network drive c) and d) – created
a “Error Link not found†in a) Link to named range.
There seems to be a limitation: “named ranges must not have the same names –
even in different workbooks with different filenames and pathsâ€!

BUG #2: Manually editing the LINK Field don’t work
This makes it impossible to create or maintain LINK Fields to spreadsheet
that for some reason not is available at the moment.
You HAVE TO reopen the workbook and manually Copy and Paste Special each
time a LINK has been broken.
It probably also makes it impossible to create and maintain LINK Fields to
spreadsheet in macros and/or code.

FLAW: Unable to open 2 workbooks with the same filename – though different
FQDN
This flaw in Excel2007 gives a serious limitation in Word2007 LINK Fields.
In testing the LINK Field it came to show, that Excel2007 has flaw:

Which – amongst other things – means that you cannot LINK to more than one
Workbook with a given name even if the other Workbook is placed a totally
different place!

---
Best regards
Erik Bo

Hi after "up"grading to Office2007B2TR links from Word to Named Ranges in
Excel no loger seems to work!

Links to Named Ranges (in casu only cells) in spreadsheets the new way:
• In Spreadsheet: [Ctrl-C] in cell
• In Word:
[Ctrl+Alt+V] (Paste Special)
[Alt+L] (Paste Link)
[Alt-A] (As)
(Unformatted Unicode Text)

Creates this field in Word-document:
{ LINK Excel.Sheet.8 "\\\\Server\\documents\\TEMP\\Book1.xlsx"
"Sheet2!Test2Name" \a \t \u }

Alt-F9 displays the content of the cell:
Test2

Which seem to work OK (a lot of new limitations though: It looks in
Office2007B2TR like the spreadsheet name must not contain space or special
characters and the Name of the range must no contain space …)

But after saving, closing and reopening the Word document IT DOES NOT WORK
ANYMORE.

When Word tries to update the link to the named range, it creates this
rather disappointing messagebox:

Word is unable to create a link to the object you specified.
Please insert the object directly into your file without creating a link.
[OK]

(Dear MS, I chose Link for a reason!)

And instead of the content of the linked cell, Word displays:
Error! Not a valid link.

The field haven’t changed. Alt-F9 displays the Field:
{ LINK Excel.Sheet.8 "\\\\Server\\documents\\TEMP\\Book1.xlsx"
"Sheet2!Test2Name" \a \t \u }

This is rather annoying as I’m using a lot of references to spreadsheets to
keep my documents up-to-date and consistent.
(I’m running Off2k7B2TR en-us on WinXPProfSP1 da-dk)

Anybody have any solutions or ideas?

 

[Bob Buckland ?:-\)]

Hi Erik Bo,

For Named ranges the rules of start with an alpha and have underscores rather than spaces appears to be the same in Excel 2007 and
Excel 2003 except that in 2007 you get a more descriptive problem solving dialog than the 'Name is not valid' one from Excel 2003

I'm not having any problem with the link still connecting on a Spreadsheet with spaces in the file name mapped/network drive (except
in the case of the separate issue you mentioned with the 'auto create backup' in Word (Excel doesn't have that choice without an
add-in in 2003 or 2007 as I recall) when you're on a network or mapped drive but when I follow your steps the field created shows a
range of cells, not a range name in the {Link...} field in Word. Are you manually replacing the cell range with a range name in your
example below?

I don't get the fields to automatically update in Word when opening the Word document even when saying [Yes] to the dialog asking if
I want the links updated, but they do update with Ctrl+A, F9 (manual field update).

=================
Hi after "up"grading to Office2007B2TR links from Word to Named Ranges in
Excel no loger seems to work!

Links to Named Ranges (in casu only cells) in spreadsheets the new way:
.. In Spreadsheet: [Ctrl-C] in cell
.. In Word:
[Ctrl+Alt+V] (Paste Special)
[Alt+L] (Paste Link)
[Alt-A] (As)
(Unformatted Unicode Text)

Creates this field in Word-document:
{ LINK Excel.Sheet.8 "\\\\Server\\documents\\TEMP\\Book1.xlsx"
"Sheet2!Test2Name" \a \t \u }

Alt-F9 displays the content of the cell:
Test2

Which seem to work OK (a lot of new limitations though: It looks in
Office2007B2TR like the spreadsheet name must not contain space or special
characters and the Name of the range must no contain space .)

But after saving, closing and reopening the Word document IT DOES NOT WORK
ANYMORE.

When Word tries to update the link to the named range, it creates this
rather disappointing messagebox:

Word is unable to create a link to the object you specified.
Please insert the object directly into your file without creating a link.
[OK]

(Dear MS, I chose Link for a reason!)

And instead of the content of the linked cell, Word displays:
Error! Not a valid link.

The field haven't changed. Alt-F9 displays the Field:
{ LINK Excel.Sheet.8 "\\\\Server\\documents\\TEMP\\Book1.xlsx"
"Sheet2!Test2Name" \a \t \u }

This is rather annoying as I'm using a lot of references to spreadsheets to
keep my documents up-to-date and consistent.
(I'm running Off2k7B2TR en-us on WinXPProfSP1 da-dk)

Anybody have any solutions or ideas?

--
Tanks in advance to anybody who care to answer
Erik Bo>>
--

Bob Buckland ?
MS Office System Products MVP

*Courtesy is not expensive and can pay big dividends*