Mass Hyperlink Updates (Office 95)

Topic

New Reply

I need to update the hyperlinks within several worksheets located in a workbook. Does anyone know of a way to update these en masse rather than individually ?

Many thanks
Phil

Reply | Quote

Replies

Search and replace?

Reply | Quote

Unfortunately not. This only finds the alternative text that we have associated with the link rather than the text of the link itself.

Thanks though !

Reply | Quote

You might use this code to find and replace in all hyperlinks of the active worksheet:

Option Explicit

Sub ReplaceInHyperlinks()
Dim oLink As Hyperlink
Dim sFind As String
Dim sReplace As String
sFind = InputBox(“Please enter the text to search for”, “Find and Replace in Hyperlinks”)
If sFind = “” Then Exit Sub
sReplace = InputBox(“Please enter the text to replace with”, “Find and Replace in Hyperlinks”)
If sReplace = “” Then
If MsgBox(“No replace text was entered, continue replacing with nothing?” _
, vbYesNo, “Find and Replace in Hyperlinks”) = vbNo Then Exit Sub
End If
For Each oLink In ActiveSheet.Hyperlinks
If InStr(oLink.Address, sFind) > 0 Then
oLink.Address = Application.WorksheetFunction.Substitute(oLink.Address, sFind, sReplace)
End If
Next
End Sub

Reply | Quote

Jan,

I tried your code and was most impressed following my own efforts on this today. I found that the replace worked fine in those hyperlinks that Excel generates itself – such as when you enter a URL and it converts it to a hyperlink.

However it does not replace entries created with the Hyperlink worksheet function, such as:

=HYPERLINK(“www.wopr.com”,”Woody’s Office Site”)

Thanks for your contributions.

Peter Moran

Reply | Quote

[indent]

---

However it does not replace entries created with the Hyperlink worksheet function, such as:

=HYPERLINK(“www.wopr.com”,”Woody’s Office Site”)

---

[/indent]

No, those can be changed using the find and replace function:
– start Find and select Formulas
– choose replace and type the find and replace text.

Reply | Quote

The code worked well. Thank you all very much for your suggestions.

Kind regards

Phil

Reply | Quote

I don’t even remember that Excel 95 supported hyperlinks .

Perhaps a poster with an older version will be able to help.

Cheers

Reply | Quote