Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.docmanagement
Erik Bo Sørensen Erik Bo Sørensen is offline
external usenet poster
 
Posts: 7
Default Can't link to Excel2007B2TR Spreadsheet Named Ranges from Word2007

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
  #2   Report Post  
Posted to microsoft.public.word.docmanagement
Erik Bo Sørensen Erik Bo Sørensen is offline
external usenet poster
 
Posts: 7
Default 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

  #3   Report Post  
Posted to microsoft.public.word.docmanagement
Bob Buckland ?:-\) Bob   Buckland ?:-\) is offline
external usenet poster
 
Posts: 2,073
Default Can't link to Excel2007B2TR Spreadsheet Named Ranges from Word2007

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).

=================
"Erik Bo Srensen" wrote in message
...
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 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*


Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
embedded Excel sheet in word document - how to use named ranges Wazooli Microsoft Word Help 3 October 26th 06 09:19 AM
Linking an Excel Spreadsheet in Word MB Microsoft Word Help 8 August 26th 06 02:39 AM
how to get around broken link? gs Microsoft Word Help 6 May 5th 06 02:39 PM
How to link a word field to an excel spreadsheet Matt Microsoft Word Help 1 January 26th 05 07:37 PM
Problem Working with Named Ranges montgomerymouse Microsoft Word Help 1 January 10th 05 11:10 AM


All times are GMT +1. The time now is 06:32 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"