Excel Hyperlink mass update

Question

I have a spreadsheet with thousands of rows. Each row contains a hyperlink with a path.

The path is not valid, however easily fixable by replacing first part of it with correct value.

Example: current hyperlink: F:\Help\index.html

Needed: P:\SystemHelp\index.html

The problem is that standard Find/Replace does not "see" content of hyperlinks.

Is the only way to write a macro or is there another way to do it?

Answer 1

I don't know of another way besides a macro. But looks like somebody already wrote one to do it.

Answer 2

Hey cnx.org, way to reinvent the Replace function.

Sub FindReplaceHLinks(sFind As String, sReplace As String, _
    Optional lStart As Long = 1, Optional lCount As Long = -1)

    Dim rCell As Range
    Dim hl As Hyperlink

    For Each rCell In ActiveSheet.UsedRange.Cells
        If rCell.Hyperlinks.Count > 0 Then
            For Each hl In rCell.Hyperlinks
                hl.Address = Replace(hl.Address, sFind, sReplace, lStart, lCount, vbTextCompare)
            Next hl
        End If
    Next rCell
End Sub

Sub Doit()

    FindReplaceHLinks "F:\help\", "F:\SystemHelp\"

End Sub