How to edit multiple hyperlinks of Excel at a single time

**De-Dg** · 24-02-2012

I have a spread sheet with over 270 hyperlinks, have had to move the spreadsheet and associated links to a new location but the hyperlinks are still looking in the old location! Does anyone know a way to edit the hyperlinks 'on mass' as opposed to me editing each link individually.

**Shurod** · 24-02-2012

Try this:

Code:

Sub ReplaceHyperlinksInActiveWorkbook()
Dim oSheet As Object
Dim H As Hyperlink
Dim stFind As String
Dim stReplace As String
stFind = InputBox("What is the initial path to replace?", , "\\Old\")
If stFind = "" Then Exit Sub
stReplace = InputBox("What should the path become?", , "\\New\")
If stReplace = "" Then Exit Sub
For Each oSheet In ActiveWorkbook.Sheets
For Each H In oSheet.Hyperlinks
If InStr(H.Address, stFind) = 1 Then
H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
End If
Next
Next
End Sub

**AnitG** · 24-02-2012

I created a VBA routine with this, Bill, and then assigned it to a button. It is not working for me. the routine runs correctly, but the hyperlinks aren't updating. The files are on our business network, so I am wondering if that has anything to do with it.

**Koman** · 24-02-2012

Presumably the stFind you specified is not matching the start of the hyperlink addresses you wanted to change. Could be a case-sensitivity issue. Try: If InStr(LCase(H.Address), LCase(stFind)) = 1 Then. If that still doesn't do it, get it to tell you what the Address is that it is finding:

Code:

For Each H In oSheet.Hyperlinks
Debug.Print H.Address
If InStr(LCase(H.Address), LCase(stFind)) = 1 Then
H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
End If
Next

**Leanne** · 24-02-2012

No great secret. The language is Visual Basic for Applications (VBA). A good book to try for starters would be Excel NNNN Visual Basic for Applications Step by Step.

**Hollyn** · 24-02-2012

Excel does not provide help for mass edits of hyperlink addresses. You will either have to use a macro similar to the one I posted on each workbook whose hyperlinks you need to change, or you could email me at Bill underscore Manville at Compuserve dot com for a free copy of LinkManager, a utility which will do this and more.