After a Paste special linking of a range of cells from Excel to Word (2013) the field looks like this:
{ LINK Excel.Sheet.12 "D:\\RelativePath\\1\\work\\tmp.xlsx" Sheet1!NamedRange \a \p }
If you move the source (.xlsx) and receiver (.docx) to the 2 folder, you need to change the link in the LINK field so that it looks like this:
{ LINK Excel.Sheet.12 "D:\\RelativePath\\2\\work\\tmp.xlsx" Sheet1!NamedRange \a \p }
When there are many such fields, it is inconvenient.
I tried both this:
{ LINK Excel.Sheet.12 "...\\...\\work\\tmp.xlsx" Sheet1!NamedRange \a \p }
and that:
{ LINK Excel.Sheet.12 "~\\work\\tmp.xlsx" Sheet1!NamedRange \a \p }
but nothing works.
That doesn't work either:
How to create absolute hyperlinks and relative hyperlinks in Word documents
Is it possible to specify in the LINK field not absolute, but relative source address?
Upd @Cindy Meister suggested a solution and after some refinement the code works fine.
Here he is:
Sub updateLINKs()
Dim doc As Word.Document
Dim fld As Word.Field
Dim sFilePath As String, sFileName As String
Set doc = ActiveDocument
sFilePath = doc.Path
For Each fld In doc.Fields
If fld.Type = wdFieldLink Then
If fld.Result.InlineShapes.Count > 0 And _
InStr(fld.OLEFormat.ClassType, "Excel") And _
fld.LinkFormat.SourcePath <> sFilePath Then
sFileName = fld.LinkFormat.SourceName
fld.LinkFormat.SourceFullName = sFilePath & "\" & sFileName
End If
End If
Next
End Sub
It's not possible to use relative paths in
Link
fields. The path needs to be updated/changed in the field code. This can be done in a reasonably straight-forward manner by using the possibilities provided by theLinkFormat
property.For example, assuming the relative path would be the same path as the Word document the following code:
Document.Path
propertyInlineShape
(an object, rather than text). The fields are looped in reverse order, since changing the path causes Word to delete and recreate the LINK in the background. This means a loop from the beginning of the document will repeatedly "hit" the same field, resulting in an endless loop.Link
field usingLinkFormat.SourceName
LinkFormat.SourceFullName
propertyNote that the
LinkFormat.SourcePath
andLinkFormat.SourceName
properties are read-only, so the path can only be changed usingSourceFullName
.