Change hyperlink location

Question

I have a database of hundreds of hyperlinks which direct to the location of file:///\My002vs0026\Department\"file name".

I wish to change the initial part "My002..0026" to "My002..0095"

I used the following codes which found on the net. When run, nothing happens.

Sub ChangeHyperlinks()
Dim h As Hyperlink 
Dim oldDr As String, newDr As String
oldDr = "my002vs0026"
newDr = "my002vs0095"
For Each h In Sheets("Sheet1").Hyperlinks
h.Address = newDr & Mid(h.Address, Len(oldDr) + 1, Len(h.Address))
Next h
End Sub

Maybe there is any other solution to change the hyperlinks location.

Excel is not a *database*. You have a worksheet with hundreds of hyperlinks. What does the debugger tell you when you step through the code? — Ken White, May 27 '16 at 03:19
hi @KenWhite, when i try to run it by pressing the f5 button. no box no instructions or any and my old hyperlinks didnt change. i go to the excel worksheet n pressed the step into button there and it yellow-highlighted my first line of my codes — qayyummarzalan, May 27 '16 at 03:40
Possible duplicate of [Error in finding last used cell in VBA](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — qayyummarzalan, Nov 09 '16 at 02:29

score 0 · Accepted Answer · answered May 27 '16 at 05:09

0

You might find it easier to perform a Replace operation on the .Address of the existing Hyperlinks object.

Option Explicit

Sub ChangeHyperlinks()
    Dim h As Long
    Dim oldDr As String, newDr As String
    oldDr = "\my002vs0026\"
    newDr = "\my002vs0095\"

    With Worksheets("Sheet1")
        For h = 1 To .Hyperlinks.Count
            With .Hyperlinks(h)
                'Debug.Print .Address
                .Address = Replace(.Address, oldDr, newDr, 1, -1, vbTextCompare)
                'Debug.Print .Address
            End With
        Next h
    End With
End Sub

This should work according to the sample that you provided but you have to be careful to avoid false positives on partial matches. Note that I've added leading and trailing back-slashes to make your search and replace terms more unique.

answered May 27 '16 at 05:09

holy it worked! thanks so much Jeeped and u too Ken White. – qayyummarzalan May 27 '16 at 06:34
Hey @Jeeped, what if I want to perform the same operation but on sheet 2 this time? which lines need to be changed, please? – qayyummarzalan May 27 '16 at 06:37
There are many examples of cycling through two or more worksheets to perform the same basic operations. Please do not turn this into a [Russian Doll Question](http://meta.stackexchange.com/questions/188625). – May 27 '16 at 06:52
Thanks @Jeeped I managed to change them on other sheets now. Appreciate it lots – qayyummarzalan May 27 '16 at 06:56

Change hyperlink location

1 Answers1