Edit external links in Word document using VBA

[mathijs]

Hi,

I have an Access database (2007) with data of different institutions. From this database a Word document can be created with data of a chosen institution. It follows these steps:

1. the user choses an institution
2. an Excel file (attached in the database) is copied to the database folder
3. the Excel file is filled with the correct institution data
4. a word document (also attached in the database) is copied to the database folder. This word document contains links to the Excel file (single cell content, tables and graphs)
5. the fields in the word document are updated
6. the links in the word document are removed and the word document is saved as a new file.

This works well, until the Access database is moved to a different folder. When this happens, the links in the Word document (pointing to the Excel file) need to be changed. The same steps as above are being taken but I take an extra step between 4 and 5:

Private Sub EditWordExternalLinks(ByRef wdDoc As Object)
    Dim newSrc As String
    Dim i As Integer


    On Error GoTo ErrorHandler


    For i = 1 To wdDoc.Fields.count
        With wdDoc.Fields(i)
            If .Type = 56 Then 'wdFieldLink
                newSrc = .LinkFormat.SourceFullName
                
                newSrc = Replace(newSrc, DATA_SOURCE(0), DATA_SOURCE(1))
                newSrc = Replace(newSrc, GetFileName(DATA_SOURCE(0)), EXCEL_FILE)
                
                .LinkFormat.SourceFullName = newSrc
                .LinkFormat.Update
                .LinkFormat.AutoUpdate = False
            End If
        End With
    Next i


ExitHandler:
    Exit Sub


ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHandler
End Sub

DATA_SOURCE(0) is the old Excel file, WORD_DATA_SOURCE(1) the new one. EXCEL_FILE is the new file name (without folder).

The code works for fields that point to single cells, but it errors if it's a figure or table (error nr. 6083: "objects contain links to files which cannot be found"). Searching for an answer, I came across changing:

wdDoc.Fields(i).Code.Text

This resulted in the same error. I also made a Word macro that changed the document's field codes, but I could not get this to work either.

Is there anyone who can point me in the right direction? I've put so much time in it already, I'm starting to think it may be impossible.