Curiously, I find that links to Excel update automatically in both Word
2003 (SP3 on Windows XP SP2) and Word 2007 (SP2 on Vista 32-bit SP2),
but on Word 2007, only when you save as .doc, not .docx and only (in
essence) when you move both the Word document and the Excel document
from one folder to another.
IN other words, Word appears to be doing the obvious thing of realising
that the Excel workbook path is no longer valid, checking that the Excel
workbook is now in the same folder as the Word document, and modifying
the link. But not if the Word document is saved as .docx (even in
compatibility mode).
Other people seem to have had a different experience, but I do not know
the origin of the differences.
AFAICS it must be Word, or some service component that Word calls that
maintains these links. I don't think it has anything to do with Excel.
I also notice that Word updates these links slightly differently when it
saves the document, depending on whether it's a docx or a doc. If it's a
docx and the workbook pathname does not contain spaces, it appears to
save the link as
{ LINK Excel.Sheet.8 "c:\\pathwithoutspaces\\wb.xls!Sheet1!R1C1:R2C2" ""
\a \p }
(Whether the linked workbook is a .xls or .xlsx)
When Word re-opens the file, you can see that it is clearly doing
/something/ with these links because it changes them to
{ LINK Excel.Sheet.8 c:\\pathwithoutspaces\\wb.xls Sheet1!R1C1:R2C2 \a \p }
You can see that there is a change by clicking Undo. With .doc, the
workbook path and "subset" name are always separate. If the pathname
contains spaces, the syntax is as follows:
{ LINK Excel.Sheet.8 "c:\\path with spaces\\wb.xls" Sheet1!R1C1:R2C2 \a \p }
However, if the link is in this format
{ LINK Excel.Sheet.8 "c:\\pathwithoutspaces\\wb.xls!Sheet1!R1C1:R2C2" ""
\a \p }
when macropod's code runs, the code will not currently work as intended
because it assumes, not unreasonably, that linkformat.sourcepath
contains the previous pathname of the linked file. But unfortunately
Word's code is buggy and just to confuse us linkformat.sourcepath does
not contain either
c:\\pathwithoutspaces\\wb.xls
i.e. what you might hope, or even
c:\\pathwithoutspaces\\wb.xls!Sheet1!R1C1:R2C2
but the even more unhelpful
c:\\pathwithoutspaces\\wb.xls!Sheet1!R1C1
I suspect some other LINKed objects may be treated in a similar way.
Perhaps worth a report to MS if an MVP can do it.
Peter Jamieson
http://tips.pjmsn.me.uk
Thanks for your reply,
I've tested what you said in your post, and you are correct. All the links
are absolute links in the old version as well. But in 2003 all the absolute
links updated to the new location where ever you pasted the new file to. Why
does that no longer happen? and is there a way to make it happen again?
macropod said:
Hi Brite,
Word links to Excel files *always* use an absolute path. Simply moving the files to another folder doesn't change where the links
point to. What you're asking for is a relative link path, which Word can't do with LINK fields (which is what it uses to link to
Excel files.
For what you want to do, you need to edit the links, for which you could use :
.. a Find/Replace operation, which you can do br pressing Alt-F9 to expose the field codes, changing the paths (not the correct
syntax for the path separators), then press Alt-F9 again to hide the field codes. Afterwards, Ctrl-A, F9 should update the linked
data; or
.. the Edit|Links icon, which you'll need to put on the Quick Access Toolbar.
Alternatively, for a macro solution, check out the code attached to my post at:
http://lounge.windowssecrets.com/index.php?showtopic=670027
--
Cheers
macropod
[Microsoft MVP - Word]
Brite said:
I'm hoping there is a solution for this issue I'm having. We recently
switched our office to office 2007 and a new problem seems to be appearing
that wasn't there before.
In our office we have an Excel document that is set up with a bunch of
calculations, and we have a word document that is also set up to go with the
calculations. In the word document we have copied and pasted all the excel
calculations into word as linked excel objects.
We have both the documents saved in the same folder which acts our template
which we can re-use over and over. So when we start a new client file we just
copy and paste the folder that contains the excel and word document to a
second location and work from it in the new location.
The problem is, in office 2007, the linked images in word are linked to the
original excel file. This means we are having to re-link all the images in
the new word document to the new excel document. This is a HUGE time waster,
and we were hoping there would be a solution to our problem. This problem
didn't exist in office 2003, so it appears to be a new issue.
Do you have any suggestions on how to get around this?
.
