none
How to Find and Replace Hyperlink Addresses in Excel RRS feed

  • Question

  • The following article works well if all hyperlinks contain the search text.

    How to replace hyperlink addresses

    If the search text is not within a hyperlink the macro bombs.

    I've tried the following addition but it never finds the text I'm looking for?  

    (i.e. pos is always zero)

    What am I doing wrong?

    Sub FixHyperlinks()
        Dim OldStr As String, NewStr As String, HL As String
        OldStr = "..\..\..\..\AppData\Roaming\Microsoft\Excel\"
        NewStr = "C:\Users\ajn38\OneDrive\Projects\Audits"
        Dim hyp As Hyperlink
        Dim pos As Integer
        For Each hyp In ActiveSheet.Hyperlinks
            HL = hyp.Address
            pos = InStr(1, hyp.Address, OldStr, 1)
            ' MsgBox (hyp.Address & " " & pos)
            If pos > 0 Then
                MsgBox ("Found!")
                hyp.Address = Replace(hyp.Address, OldStr, NewStr)
            End If
        Next hyp
    End Sub
    

    Thanks, AjN3806

    Wednesday, February 15, 2017 6:50 AM

Answers

  • AjN,
    re:  works sometime

    Insert a Stop and check the value of the HL variable.  Does it contain OldStr?

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Marked as answer by AjN3806 Wednesday, February 15, 2017 11:07 PM
    Wednesday, February 15, 2017 5:29 PM

All replies

  • AjN,
    re:  works sometime

    Insert a Stop and check the value of the HL variable.  Does it contain OldStr?

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Marked as answer by AjN3806 Wednesday, February 15, 2017 11:07 PM
    Wednesday, February 15, 2017 5:29 PM
  • Hi James,

    thanks for the advice, after setting a stop and adding watches, I could see that the slashes in the hyperlink address were not back slashes "\", but forward slashes "/".  Now the text is found.

    Thanks again, AjN3806

    Wednesday, February 15, 2017 10:45 PM
  • I actually had to cater for both forward and back slashes.  Final code...

    Sub FixHyperlinks()
        Dim OldStr As String, NewStr As String
        OldStrF = "../../../../AppData/Roaming/Microsoft/Excel/"
        NewStrF = "C:/Users/ajn38/OneDrive/Projects/Audit/"
        OldStrB = "..\..\..\..\AppData\Roaming\Microsoft\Excel\"
        NewStrB = "C:\Users\ajn38\OneDrive\Projects\Audit\"
        Dim hyp As Hyperlink
        Dim posF As Integer, posB As Integer
        For Each hyp In ActiveSheet.Hyperlinks
            posF = InStr(1, hyp.Address, OldStrF, vbTextCompare)
            If posF > 0 Then
                hyp.Address = Replace(hyp.Address, OldStrF, NewStrF)
            End If
            posB = InStr(1, hyp.Address, OldStrB, vbTextCompare)
            If posB > 0 Then
                hyp.Address = Replace(hyp.Address, OldStrB, NewStrB)
            End If
        Next hyp
    End Sub
    
    

    Wednesday, February 15, 2017 11:10 PM