Can't Link Excel to Word with Named Ranges

[Sean]

Neither Office 2003 (since SP3) nor Office 2007, will properly link Word to
Excel tables using named ranges.

I produce reports containing dozens of linked Excel tables. It is essential
that these links be maintained while the Excel tables are being modified. For
that reason I always use named ranges. This has always worked OK in Office
2003 until recently I started getting the message: "Word cannot obtain the
data for the Excel.Sheet.8 Link". Getting around this was painful, I have to
un-name the range. make the link, name the range and then edit the link -
very tedious and annoying. A lot of people seem to have this problem but
no-one seems to have found a solution.

I recently upgraded to Office 2007 (SP1), which has been a costly and
time-consuming process. To my absolute horror I find that the Word-Excel
linkage still does not work. I don't get the error-message, but if I look at
the linkage, I find it is using the RC reference and not the Range-Name -
Absolutely Useless!

Does anyone have a solution?

Sean

 

[Bob Buckland ?:-\)]

Hi Sean,

It's a problem that Microsoft is aware of in SP3 of Office 2003, although I don't recall it being reported here for Office 2007,
and I haven't had a particular problem linking to named ranges using Paste Special Link.

It is possible as some of the Office 2003 service pack changes are 'backfit' security settings from Office 2007. At present,
pending an update, there are workarounds, but they're either a security tradeoff or a bit cumbersome for Word 2003 SP3.

1. Use the cell range rather than a named range to pick and paste

2. Once you have a working link, Press Alt+F9 in Word to view field codes and add in the named range.

3. One person mentioned modifying the registry after deciphering the 'new feature' information in
http://support.microsoft.com/kb/938815

If you create a new Word 2007 document and Excel 2007 workbook, create a named range and place it into the Word 2007 document are
you getting the problem? What is the field code you see if a problem using Alt+F9 in the Word document?

===============
Neither Office 2003 (since SP3) nor Office 2007, will properly link Word to Excel tables using named ranges.

I produce reports containing dozens of linked Excel tables. It is essential that these links be maintained while the Excel tables
are being modified. For that reason I always use named ranges. This has always worked OK in Office 2003 until recently I started
getting the message: "Word cannot obtain the data for the Excel.Sheet.8 Link". Getting around this was painful, I have to un-name
the range. make the link, name the range and then edit the link -
very tedious and annoying. A lot of people seem to have this problem but
no-one seems to have found a solution.

I recently upgraded to Office 2007 (SP1), which has been a costly and
time-consuming process. To my absolute horror I find that the Word-Excel
linkage still does not work. I don't get the error-message, but if I look at the linkage, I find it is using the RC reference and
not the Range-Name -
Absolutely Useless!

Does anyone have a solution?

Sean >>
--

Bob Buckland ?
MS Office System Products MVP

*Courtesy is not expensive and can pay big dividends*

 

[Sean]

Bob;

Yes, the workaround is tedious and should not be necessary. When I look with
Alt-F9, or with Edit\Links, I see the RC reference, as opposed to the
Range-Name. After I type in the Range-Name, it accepts it, and appears to
work properly. Interestingly, it does not pick up the capitalization of the
name: If the Range Name is "Rep_Table1", and I type it in all lower-case, it
appears as ".....\rep_table1". It used to correct the capitalization.

I have thought of completely uninstalling Office and reinstalling up to SP2
but I suspect it may be a waste of time (I think others have tried it). SP3
did fix a problem I had been having with Lookup functions, but the trade-off
wasn't worth it!

I had also looked at the article you mention
(http://support.microsoft.com/kb/938815) but it made no sense to me! I
couldn't tell if it was even related to the problem. Besides which, if the
new feature is disabled by default, then it shouldn't have any impact until
you actively enable it (I would have thought).

It's interesting that you don't have the problem & strongly suggests it is a
legacy of 2003/SP3. I will try opening a new Word & Excel file to see if it
works. First I will have to remind myself how to preserve my Excel & Word
2003 settings (particularly the toolbars) as they get blown away every time
Word2007 registers itself.

I'll let you know how it goes.

Thanks,
Sean

 

[Sean]

Bob;

Further to my earlier message, I tried it again with a completely new
Document and Table - And it worked!!! I then re-opened a pair of files that
had been giving bother and it worked on them too!

I had de-registered and re-registered 2007 a few times so I don't know if
that did it or trying it with new files.

I'm not celebrating yet - I suspect Office 2007 is just trying to regain my
confidence so that I will commit work to it and it can start driving me crazy
again.

Fingers crossed

Sean

 

[Chuck]

I've had the same problem as Sean. Thanks for the workaround; however, I
tried embedding the spreadsheet using insert object from Excel and SP3 won't
allow that either. I suspect that SP3 has done a lot of damage to Office and
disabled many functions that were available before. I was warned not to
install SP3 yet. I wish I had listened. It appears that Microsoft is no
longer offering SP2, so it doesn't look like one can uninstall SP3 and
reinstall SP2.

 

[Ken]

Neither Office 2003 (since SP3) nor Office 2007, will properly link Word to
Excel tables using named ranges.

I produce reports containing dozens of linked Excel tables. It is essential
that these links be maintained while the Excel tables are being modified. For
that reason I always use named ranges. This has always worked OK in Office
2003 until recently I started getting the message: "Word cannot obtain the
data for the Excel.Sheet.8 Link". Getting around this was painful, I have to
un-name the range. make the link, name the range and then edit the link -
very tedious and annoying. A lot of people seem to have this problem but
no-one seems to have found a solution.

I recently upgraded to Office 2007 (SP1), which has been a costly and
time-consuming process. To my absolute horror I find that the Word-Excel
linkage still does not work. I don't get the error-message, but if I look at
the linkage, I find it is using the RC reference and not the Range-Name -
Absolutely Useless!

Does anyone have a solution?

Sean

 

[Ken]

Neither Office 2003 (since SP3) nor Office 2007, will properly link Word to
Excel tables using named ranges.

I produce reports containing dozens of linked Excel tables. It is essential
that these links be maintained while the Excel tables are being modified. For
that reason I always use named ranges. This has always worked OK in Office
2003 until recently I started getting the message: "Word cannot obtain the
data for the Excel.Sheet.8 Link". Getting around this was painful, I have to
un-name the range. make the link, name the range and then edit the link -
very tedious and annoying. A lot of people seem to have this problem but
no-one seems to have found a solution.

I recently upgraded to Office 2007 (SP1), which has been a costly and
time-consuming process. To my absolute horror I find that the Word-Excel
linkage still does not work. I don't get the error-message, but if I look at
the linkage, I find it is using the RC reference and not the Range-Name -
Absolutely Useless!

Does anyone have a solution?

Sean

Yes, the solution we have found is simply to remove SP3 update. This has
worked so far (as of Mar.08) but we have every confidence the MS will screw
it up in a future "Security" update.

 

[trishash]

There is a new hotfix that should be released in the next few days. Tired of
waiting, we called Microsoft this morning and got it. It actually works!!
Should be KB951405 when released.

 

[trishash]

Update: Not quite sure if it breaks other things though. We're having a few
new issues after installing this hotfix on some machines. When we determine
whether these issues were actually because of the hotfix, I'll post again.

 

[Bob Buckland ?:-\)]

Hi Trish,

Please do advise of other items you think the hotfix may have broken in Office 2003+SP3. It does appear to have fixed the named
range/clipboard issue. I've not tried it on Vista.

===============Update: Not quite sure if it breaks other things though. We're having a few new issues after installing this hotfix on some
machines. When we determine whether these issues were actually because of the hotfix, I'll post again. >>
--

Bob Buckland ?
MS Office System Products MVP

*Courtesy is not expensive and can pay big dividends*

 

[trishash]

We are still working with Microsoft to resolve an issue related to the named
range issue. It seems to be a conflict with some of the other accounting
programs we are using. Two add-ins used by these other programs are removed
each time a user updates the links.

 

[L Sangree]

Does this hotfix seem to be the answer? Should we apply it? Does it cause any
problems other than with the add-ins?

 

[trishash]

Yes, it fixed our issue with linking. We haven't found any other problems
besides the add-in.

 

[Bob Buckland ?:-\)]

Hi Trish,

Is it a 3rd party add-in and has it been determined that the hotfix created the issue, or if it's the add-in?

===================
Yes, it fixed our issue with linking. We haven't found any other problems
besides the add-in. >>
--

Bob Buckland ?
MS Office System Products MVP

*Courtesy is not expensive and can pay big dividends*

 

[trishash]

Yes, 3rd party add-in. We didn't have the issue until applying the hotfix,
but it's not as big a problem as the linking issue. We're working with the
software for a solution.

 

[Brian S.]

I too have had this problem in SP3, but with no error message appearing. It will only paste the RxCx link in Word. This doesn't happen on other computers that don't have SP3. I've tried 2 fixes (registry and patch) and neither worked to alleviate the problem.

Since there's no option to remove SP3 in Add/Remove Programs, the solution is to uninstall Office 2003 completely, then reinstall from CD (up to SP2 / without SP3).

 

[Terry Farrell]

There was a bug with the ServicePack but I understand that this has been
fixed with the latest updates. So run Windows Updates again which should
install al the Office patches.

 

[alex burn]

Not sure if people are still experiencing this problem, but I was. Have just done some testing and found the following results:-

I have a named range in Excel called "TestRange".
This is on a sheet called "ControlSheet".

I went into Word to paste link and the reference appeared as RC ref style.

I tried changing the sheet name to "Sheet1" - this is the only change I made.

I went into Word to paste link and the reference appeared correctly as "Sheet1!TestRange".

It seems that named ranges pasted as links only work if the sheet name is in the format "SheetX". Custom naming the sheet prevents the link to a named range from being recognised.

I am using Word & Excel versions 2007.

Regards

 

[alex burn]

Not sure if people are still experiencing this problem, but I was. Have just done some testing and found the following results:-

I have a named range in Excel called "TestRange".
This is on a sheet called "ControlSheet".

I went into Word to paste link and the reference appeared as RC ref style.

I tried changing the sheet name to "Sheet1" - this is the only change I made.

I went into Word to paste link and the reference appeared correctly as "Sheet1!TestRange".

It seems that named ranges pasted as links only work if the sheet name is in the format "SheetX". Custom naming the sheet prevents the link to a named range from being recognised.

I am using Word & Excel versions 2007.

Regards