How to specify the relative path to the workbook in the LINK field?

Question

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

Answer 1

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 the LinkFormat property.

For example, assuming the relative path would be the same path as the Word document the following code:

1. Gets the path from the Document.Path property
2. Loops all the fields in the document and determines whether they're a LINK field from an Excel source that displays an InlineShape (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.
3. Determines the current file name for the linked object from the Link field using LinkFormat.SourceName
4. Concatenates the information and assigns it to the LinkFormat.SourceFullName property

Note that the LinkFormat.SourcePath and LinkFormat.SourceName properties are read-only, so the path can only be changed using SourceFullName.

Sub UpdateLinks()
    Dim doc As Word.Document
    Dim fld As Word.Field, nrFields As Long, i As Long
    Dim sFilePath As String, sFileName As String

    Set doc = ActiveDocument
    sFilePath = doc.path
    nrFields = doc.Fields.Count
    For i = nrFields To 1 Step -1
        Set fld = doc.Fields(i)
        If fld.Type = wdFieldLink Then
          If fld.result.InlineShapes.Count > 0 And _
             InStr(fld.OleFormat.ClassType, "Excel") Then
               sFileName = fld.LinkFormat.SourceName
               fld.LinkFormat.SourceFullName = sFilePath & "\" & sFileName
          End If
        End If
    Next
End Sub

Answer 2

You cannot specify a relative path in a LINK field. That would require embedding another field in the LINK field but, as I've said previously, you can't do that with LINK fields. The link you posted concerns only the setting of a hyperlink base for hyperlinks, which also doesn't result in a truly relative link.

The only workaround for a LINK field is to use a macro that updates the absolute path.