REsearch Findings on LINK Field to Excel2007B2TR Spreadsheet - BUG
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 LINKs [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 dont 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 dont 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
"Erik Bo Sørensen" wrote:
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)
[u] (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 havent 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 Im using a lot of references to spreadsheets to
keep my documents up-to-date and consistent.
(Im 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
|