Update linked fields in Word document from Excel VBA

Question

I am trying to automatically update certain information (such as names, dates and numbers) across 3 different Word documents by putting the data into a spreadsheet and linking to the respective cells from Word. The spreadsheet has some Macros in it which auto-update parts of the spreadsheet internally.

Everything is working fine, except for updating the links in the Word documents. When trying to update a link in Word by right-clicking on it and selecting the "update link" option it brings up the Macro warning dialog for the spreadsheet, asking whether I want to activate Macros or not. It doesn't do this just once but constantly during the 20s or so the update process takes (which seems unusually long to me). So updating the link works, but only if you're willing to click the "activate Macros" button of a few dozen times.

I tried to automate updating all fields in a document from Word with VBA, but that has the same problem, it also brings up the Macros dialog constantly for half a minute. Here's my code for that:

Sub UpdateFields()
    ActiveDocument.Fields.Update
End Sub

I also tried to update the Word documents directly from the spreadsheet, but that does not work either, because when Excel tries to open a Word document via VBA the program stops executing and trows this error:

"Excel is waiting for another application to complete an OLE action."

Clicking ok and waiting does not help because the error message reappears after a few seconds, and the only way to stop it is to manually kill the Excel process.

Here's my Excel Macro code:

Sub LoopThroughFiles()
    Path = Application.ActiveWorkbook.Path
    Dim WordFile As String
    WordFile = Dir(Path & "\*.doc")
    Do While Len(WordFile) > 0
        Run Update(Path & "\" & WordFile)
        WordFile = Dir
    Loop
End Sub

Function Update(Filepath As String)

    Dim WordDoc As Word.Document
    Set WordApplication = CreateObject("Word.Application")
    Set WordDoc = WordApplication.Documents.Open(Filepath) 'This produces the error

    ActiveDocument.Fields.Update

End Function

Note that the only files in the folder are the 3 documents and the spreadsheet, and the program does find the files without any problems.

I have searched for solutions online but I did not really find anything, which I found odd, since it seems like a pretty common thing that someone would do with VBA. Then again, I have very little experience with VBA, so I might be completely missing the point and there is a super simple solution I am just not aware of.

Answer 1

I think I see the error, which is a silent failure, becuase the document contains links, there is an open dialog waiting for you to say "yes" or "no" to update the links.

We can suppress this dialog by disabling the automatic link updates (WordApplication.Options.UpdateLinksAtOpen = False).

Function Update(Filepath As String)
    Dim WordApplication As Word.Application
    Dim WordDoc As Word.Document
    Dim updateLinks As Boolean

    Set WordApplication = CreateObject("Word.Application")
        updateLinks = WordApplication.Options.UpdateLinksAtOpen 'capture the original value
        WordApplication.Options.UpdateLinksAtOpen = False      'temporarily disable

    Set WordDoc = WordApplication.Documents.Open(Filepath)
        WordDoc.Fields.Update
        'MsgBox "Links updated in " & WordDoc.Name
        '## Save and Close the Document
        WordDoc.Save
        WordDoc.Close

    '## reset the previous value and Quit the Word Application
    WordApplication.Options.UpdateLinksAtOpen = updateLinks             '
    WordApplication.Quit

End Function

Also, remember to Save and Close the document, and Quit the word application inside the function.

I made this other modification:

In your function, ActiveDocument is not an object in Excel, so you would need to qualify it, otherwise that line will also throw an error. Rather than refer to WordApplication.ActiveDocument, I just simply refer to the WordDoc which you have already assigned.