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:
- the user choses an institution
- an Excel file (attached in the database) is copied to the database folder
- the Excel file is filled with the correct institution data
- 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)
- the fields in the word document are updated
- 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.