Change multiple Hyperlinks

[FrankInBerlin]

Hi everybody!

I have a sheet in Excel 97 with 150 Hyperlinks.
They all start with something like

http://123.456.789.000/blah/blah/xxx/blah...

Now the adress has changed to

http://123.456.789.000/blah/blah/yyy/blah...

Is there a way to do something like a find and replace for all the links at once????

REgards, Frank

 

[Hasit]

The REPLACE function (i.e. EDIT-REPLACE or CTRL-H) should do this for you.

 

[FrankInBerlin]

No, that does not work!

My hyperlink text as displayed in the cell (like: Click here to get to xxx) would be edited but not the adress behind it.

If I search for

http://123.456.789.000/blah

I don't get a hit because this string is only present in the link but not in the text displayed in the cell.

Regards, Frank

 

[Hasit]

Understand now. YOu have hidden a hyperlink behind a descriptive word as opposed to presenting the hyperlink itself.

Sorry! My method would work if the hyperlink is visible, and not hidden behind a word (or words). I am not aware of a way to make a wholesale change/replace of hyperlinks set up the way you describe.

 

[JustinEzequiel]

try something like this

Option Explicit

Sub test()
  Dim oWS As Worksheet
  Dim oLink As Hyperlink
  Dim sOldStart As String
  Dim sNewStart As String
  Dim sOldAddress As String
  
  sOldStart = "J:\"
  sNewStart = "C:\"
  
  Set oWS = ActiveWorkbook.ActiveSheet
  
  For Each oLink In oWS.Hyperlinks
    sOldAddress = oLink.Address
    If LCase$(Left$(sOldAddress, Len(sOldStart))) = LCase$(sOldStart) Then
      oLink.Address = sNewStart & Mid$(sOldAddress, Len(sOldStart) + 1)
    End If
  Next oLink
End Sub

 

[FrankInBerlin]

Thanks for giving me this hint.
I change it somewhat to be more flexible...

Sub Hyperlink_change()
Dim oWS As Worksheet
Dim oLink As Hyperlink
Dim sOldAdr As String
Dim sNewAdr As String
Dim sOldAddress As String
Dim Position1 As Long
Dim Position2 As Long

sOldAdr = "xxx"
sNewAdr = "yyy"

Set oWS = ActiveWorkbook.ActiveSheet

For Each oLink In oWS.Hyperlinks
sOldAddress = oLink.Address
If LCase$(InStr(sOldAddress, sOldAdr)) <> 0 Then
Position1 = InStr(sOldAddress, sOldAdr) - 1
Position2 = Len(sOldAddress) - (Position1 + Len(sOldAdr))
oLink.Address = Left(sOldAddress, Position1) & sNewAdr & Right(sOldAddress, Position2)
End If
Next oLink
End Sub

Thanks again!

Frank

 

[JustinEzequiel]

glad to be of help