Replace part of hyperlink

Question

I need to modify a lot of hyperlinks in an .xls workbook. My links are like this:

\\\mysrv001\some\path\documents.doc and I need to replace \\\mysrv001 with \\\mysrv002

I tried something like this, but I get an error: "Object doesn't support this property or method". How do I fix this?

Sub test()
    Dim hLink As Hyperlink
    Dim wSheet As Worksheet
    Dim path As String

    For Each wSheet In Worksheets
       For Each hLink In wSheet.Hyperlinks
            path = Right(hLink, Len(hLink) - 11)
            hLink.Address = "\\mysrv003\" & path
        Next hLink
    Next
End Sub

PS: I'm using Office 2000

Answer 1

try this

Sub test()
    Dim hLink As Hyperlink
    Dim wSheet As Worksheet

    For Each wSheet In Worksheets
       For Each hLink In wSheet.Hyperlinks
            hLink.Address = Replace(hLink.Address, "\\mysrv001\", "\\mysrv002\")
        Next hLink
    Next
End Sub

Answer 2

Whoops! You're extracting and keeping the left part of your path string, where what you really want to do is to discard it!

EDIT: Also, you can't use string functions (Left, Right, Len...) on a Hyperlink object like that. This is what is causing the error. You have to extract the Hyperlink object's Address property -- that's a string.

Replace

path = Left(hLink, 11) ' throws error: Object doesn't support this property...

with

path = Mid(hLink.Address, 12) ' returns "some\path\documents.doc"
' or, equivalently:
'path = Right(hLink.Address, Len(hLink.Address) - 11)