Issue with Excel links to .doc format Word documents

Hi Stephen,

Thank you for reaching out.Your testing points to the older Word 97–2003 .doc format as the source of the problem, rather than the Excel hyperlink itself. Since the same document opens correctly after being saved as .docx, converting the files in bulk is the most practical workaround. You do not need to open and save all 145 documents manually.

1. Back up the document folder

Before converting anything, make a copy of the folder containing the .doc files. The macro below creates new .docx files and leaves the original .doc files unchanged, but having a backup is still recommended.

If any of the .doc files contain macros, they should be reviewed separately because the standard .docx format does not retain VBA macros.

2. Run a batch-conversion macro in Word

In Word:

• Open a blank document in Word.
• Press Alt + F11.
• Select Insert > Module.
• Paste the following macro:

Sub BatchConvertDocToDocx()
    Dim folderPath As String
    Dim sourceFile As String
    Dim targetFile As String
    Dim fileName As String
    Dim wordDoc As Document
    Dim fileSystem As Object
    Dim convertedCount As Long
    Dim skippedCount As Long
    Dim failedCount As Long
    Dim failedFiles As String
    Set fileSystem = CreateObject("Scripting.FileSystemObject")
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the folder containing the DOC files"
        If .Show <> -1 Then
            MsgBox "No folder was selected.", vbInformation
            Exit Sub
        End If
        folderPath = .SelectedItems(1)
        If Right(folderPath, 1) <> "\" Then
            folderPath = folderPath & "\"
        End If
    End With
    Application.ScreenUpdating = False
    Application.DisplayAlerts = wdAlertsNone
    fileName = Dir(folderPath & "*.doc")
    Do While fileName <> ""
        sourceFile = folderPath & fileName
        targetFile = folderPath & _
            fileSystem.GetBaseName(fileName) & ".docx"
        On Error GoTo ConversionError
        If Not fileSystem.FileExists(targetFile) Then
            Set wordDoc = Documents.Open( _
                FileName:=sourceFile, _
                ConfirmConversions:=False, _
                ReadOnly:=True, _
                AddToRecentFiles:=False, _
                Visible:=False)
            wordDoc.SaveAs2 _
                FileName:=targetFile, _
                FileFormat:=wdFormatDocumentDefault, _
                AddToRecentFiles:=False
            wordDoc.Close SaveChanges:=False
            Set wordDoc = Nothing
            convertedCount = convertedCount + 1
        Else
            skippedCount = skippedCount + 1
        End If
ContinueLoop:
        On Error GoTo 0
        fileName = Dir()
    Loop
    Application.DisplayAlerts = wdAlertsAll
    Application.ScreenUpdating = True
    MsgBox convertedCount & " file(s) converted." & vbCrLf & _
           skippedCount & " file(s) skipped." & vbCrLf & _
           failedCount & " file(s) failed." & _
           IIf(failedFiles <> "", vbCrLf & vbCrLf & _
           "Failed files:" & vbCrLf & failedFiles, ""), _
           vbInformation, "Conversion complete"
    Exit Sub
ConversionError:
    failedCount = failedCount + 1
    failedFiles = failedFiles & fileName & _
        " - Error " & Err.Number & ": " & Err.Description & vbCrLf
    If Not wordDoc Is Nothing Then
        wordDoc.Close SaveChanges:=False
        Set wordDoc = Nothing
    End If
    Err.Clear
    Resume ContinueLoop
End Sub

• Place the cursor inside the macro.
• Press F5.
• Select the folder containing the 145 .doc files.

3. Update the Excel Links

• After conversion, the Excel hyperlinks must point to the new .docx files rather than the original .doc files. If the links are normal worksheet hyperlinks, they can also be updated in bulk with an Excel macro.
• Save a backup copy of the workbook first, then run this macro from Excel using Alt + F11 > Insert > Module.
• Paste the following macro:

Sub UpdateDocLinksAndDisplayText()

    Dim ws As Worksheet
    Dim link As Hyperlink
    Dim oldAddress As String
    Dim oldText As String
    Dim updatedCount As Long

    For Each ws In ThisWorkbook.Worksheets
        For Each link In ws.Hyperlinks

            oldAddress = link.Address

            If LCase(Right(oldAddress, 4)) = ".doc" Then

                link.Address = Left(oldAddress, Len(oldAddress) - 4) & ".docx"

                If link.Type = msoHyperlinkRange Then
                    oldText = link.Range.Text

                    If LCase(Right(oldText, 4)) = ".doc" Then
                        link.TextToDisplay = _
                            Left(oldText, Len(oldText) - 4) & ".docx"
                    End If
                End If

                updatedCount = updatedCount + 1

            End If

        Next link
    Next ws

    MsgBox updatedCount & _
        " hyperlink(s) updated successfully.", _
        vbInformation, "Update complete"

End Sub

• Place the cursor inside the macro.
• Press F5.
• Right-click each link and select Edit Hyperlink.
• Confirm that the paths now end in .docx.
• Click each link and verify that the correct converted document opens.

I hope your issue gets resolved soon. Any updates would be greatly appreciated.