I have an excel spreadsheet with some data and related tables & charts, and I also have some associated word documents with links back to these excel objects.

If the files are moved then the links become broken, so I have written a VBA routine to update these links, using code ideas widely available on the web. This works fine – but excel hangs for about 10-15 seconds after the routine completes. I cannot find the reason why.

Code is here:

Sub ConnectReportDocument()
'
Dim objWdApp As Object
Dim objWdDoc As Object
Dim iFieldCount As Integer
Dim i As Integer
Dim sWordFilePath As String
Dim dialog As FileDialog
'
'
' Prompt the user for the document to be connected to this workbook
'
    Set dialog = Application.FileDialog(msoFileDialogFilePicker)
    With dialog
        .Title = "Select the Word Document to be linked to this workbook"
        .Filters.Clear
        .Filters.Add "Word Documents", "*.doc; *.docx; *.docm"
        .AllowMultiSelect = False
        If .Show = -1 Then
            sWordFilePath = .SelectedItems(1)
        Else
            MsgBox "No file selected. Operation cancelled."
            Exit Sub
        End If
    End With
'
'
' Open the selected Word document
'
    On Error Resume Next
    Set objWdApp = GetObject(, "Word.Application")
    If objWdApp Is Nothing Then
        Set objWdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
'
'
' Disable alerts when we open the file, then re-enable
'
    objWdApp.DisplayAlerts = wdAlertsNone
    objWdApp.Visible = True
    Set objWdDoc = objWdApp.Documents.Open(sWordFilePath)
    objWdApp.DisplayAlerts = wdAlertsAll

    With objWdDoc
       iFieldCount = .Fields.Count
          For i = 1 To iFieldCount
          With .Fields(i)
             If .Type = 56 Then
                .LinkFormat.SourceFullName = ThisWorkbook.FullName
                .Update
                DoEvents
             End If
          End With
       Next i
    End With
'
'
' Save and close the Word document, and then clean up
'
    objWdDoc.Save
    objWdDoc.Close
    objWdApp.Quit
'
    Set objWdDoc = Nothing
    Set objWdApp = Nothing

End Sub

I have searched for any similar issue on stack overflow and elsewhere but can find nothing. I’m wondering whether excel is actually stuck waiting for something, and then timing out – but I can’t see what.

There is only one instance of word running when this happens, and it completes and exits promptly at the end of the VBA routine.