Excel => Paste Link => Word

[dsc]

Office 2003: Attempts to paste a named range from XL to Word as a linked
picture no longer works after SP3. Method used is to first copy the range
name (table) from XL, then use Paste Special | Paste Link | As Picture
(Windows Metafile) in Word. Has anyone else experienced this or found a
work-around? The error message is "Word cannot obtain the data for the
Excel.Sheet.x link." The odd thing is that by removing the range name and
copying by Row/Column reference, the same method will work. Any
ideas/suggestions/comments appreciated.

FYI, this procedure works fine again after uninstalling & reinstalling
Office up through SP2.

 

[cl]

We have exactly the same problem. We use a custom wizard that makes use of
named ranges in Excel, and it has the problem you described.

Also, when doing the manual Copy/paste link of a named range, we get the
same error. I'm also looking for a solution.

cl

 

[trishash]

Reinstalling to SP2 did not help here. We no longer saw the error message,
but the link would not work. A coworker found this workaround, and it seems
to work, but I haven't gotten one of our users to try it yet.

In Excel, enter data (do not name) and save worksheet.
Copy range, and paste special into Word as usual.
Save Word doc.

Go back to Excel and name range. Save worksheet.
Go to Word and press “ALT-F9†to bring up the link code.
Should look something like this --- { LINK Excel.Sheet.x
“C:\\Documents…\\Bookx.xls†“Sheet1!R1C1:R8C3†\a \p }

After Sheet1! Replace the range info (R1C1:R8C3) with the name you gave the
range. Press “ALT-F9 again to return to data. Save and close the Word doc.

Reopen the Word doc. Make changes as needed to Excel data and see changes
in Word doc. Save and close.

 

[dsc]

Unfortunately, my XL files already include numerous pre-defined names, so the
work-around is not practical for me. Perhaps it will help others though.

 

[Sean]

I had the same problem with Office 2003 (ever since SP3) and got around it by
editing the field afterwards, which was a very tedious and unacceptable
workaround. I recently upgraded to Office 2007, hoping that linking named
ranges would work properly - IT DOES NOT WORK! if the range is named and you
Paste-Special\ Link \Excel Object - it only inserts the RC reference and not
the range-name. Moving to 2007 is painful enough without finding out it has
been crippled the same way as 2003!

Any ideas?

Sean

 

[dsc]

Yes, I believe that 2007 does away with named ranges, which is probably why
SP3 causes this particular problem. As you've already discovered, you can
copy an existing link and then manually edit the field to point to the new
range name, but that isn't viable for the vast majority of my users. As I
still have yet to find a realistic solution, I am simply advising my users
not to install SP3.

 

[Sean]

Well, its very strange, but someone asked me if named-range linking worked in
a completely new XL table and Word document. I tried it, and it did work.
Since then, it's been working on older documents as well! Although I am still
wary, the problem seems to have fixed itself!

I don't know where you got the impression that named-ranges have been
discontinued in XL2007. As far as I can see, they still work as before,
although the process of defining, re-defining them & so on has been
considerably complicated (in some ways for the better).

I must confess, I do like much of what XL2007 offers, particularly the
bigger spreadsheets. Mind you, I think the Ribbon would drive me nuts if I
didn't remember the menu commands from 2003 (which still work, although you
don't see the old menu).

Whatever happens, I won't be uninstalling XL2003 any time soon as I cannot
do without import/export of Lotus 123 and dBase files, which XL2007 has
discontinued.

Regards,
Sean

 

[dsc]

My bad...meant to say range labels, not range names. Some elements of our
workbooks were designed years ago.

I tried creating a new workbook w/new range name and paste into a new doc,
however, received the same old error message. Good to know that it's working
for you in 2007 though. Maybe I'll try it again in XP without any XL
add-in's or customized Word templates auto-loaded.

 

[Sean]

I still have Office 2003 on my system because I can't live without support
for Lotus 123 and dBase files and because so many things in Office 2007
simply don't work properly (don't get me started on editing shapes &
charts!). Consequently, at least to begin with, I was switching from 2003 to
2007 and back. Since Word registers itself every time that was a painful
process, but in the meantime those repeated registrations may have shaken off
the legacy of SP3 which seemed to have attached itself to 2007. I had also
manually registered XL2003 a few times as the default XL to use when
double-clicking (when Word 2007 was registered) - maybe that helped too.

Sean

 

[Bob Buckland ?:-\)]

Hi Sean,

While there is an issue with this in Word 2003 + SP3, I'm not sure why you're having an issue with this in Excel 2007/Word 2007,
unless something is getting 'mixed' if you have the two versions together and is out of sync.

Whether I Paste Special=>Link a named range from Excel into Word 2007
as either HTML Format or Excel Object I can type in a cell in Excel within the named range and on hitting Enter watch the changes
occur in Word.

Using Alt+F9 in Word to look at the Link fields both of these show the range name.

What happens if you create a new Word 2007 document and a new Excel 2007 worksheet and create a named range within it and then copy
and paste the named range into Word as a link?

Are you running any add-ins in Word or Excel 2007?

============
I had the same problem with Office 2003 (ever since SP3) and got around it by
editing the field afterwards, which was a very tedious and unacceptable
workaround. I recently upgraded to Office 2007, hoping that linking named
ranges would work properly - IT DOES NOT WORK! if the range is named and you
Paste-Special\ Link \Excel Object - it only inserts the RC reference and not
the range-name. Moving to 2007 is painful enough without finding out it has
been crippled the same way as 2003!

Any ideas?

Sean >>
--

Bob Buckland ?
MS Office System Products MVP

*Courtesy is not expensive and can pay big dividends*

 

[Sean]

Bob;
I have managed to get rid of this problem, although I'm not sure what did
the trick (see my messages above). Creating new files did seem to work for
me, but not for others (again, see above).
When I first installed XL2007 I added in the Analysis Toolpack, but removed
it as soon as I realized it was a built-in function. I don't think that had
anything to so with it, although it may have contributed to a problem I have
been having with Networkdays() formulae.
Thanks,
Sean

 

[dsc]

Bob, you say that this is a known issue with '03 + SP3, so I take it that
modifying the registry per KB938815 won't help? Reason I ask is that I tried
it (at least I think I did) by adding the OLE create and OCX insert to the
"allow" list and the paste link process still failed. Just wondering if I
should be holding my breath for a "patch" anytime soon...