Find and Replace Hyperlinks
Sub FindReplaceHLinks(sFind As String, sReplace As String, _
Optional lStart As Long = 1, Optional lCount As Long = -1)
Dim rCell As Range
Dim hl As Hyperlink
For Each rCell In ActiveSheet.UsedRange.Cells
If rCell.Hyperlinks.Count > 0 Then
For Each hl In rCell.Hyperlinks
hl.Address = Replace(hl.Address, sFind, sReplace, lStart, lCount, vbTextCompare)
Next hl
End If
Next rCell
End Sub
Optional lStart As Long = 1, Optional lCount As Long = -1)
Dim rCell As Range
Dim hl As Hyperlink
For Each rCell In ActiveSheet.UsedRange.Cells
If rCell.Hyperlinks.Count > 0 Then
For Each hl In rCell.Hyperlinks
hl.Address = Replace(hl.Address, sFind, sReplace, lStart, lCount, vbTextCompare)
Next hl
End If
Next rCell
End Sub
Sub Doit()
FindReplaceHLinks “F:help”, “F:SystemHelp”
End Sub
Who knows why Excel has a hyperlink collection if you can only have one per cell.
this is a little simpler (from the helpfile example)
For Each h in ActiveSheet.Hyperlinks
Dick -
The Hyperlinks collection can be a property of a Range (one or more cells) or of a worksheet.
Dick,
Here is how I would write it:
Optional lStart As Long = 1, Optional lCount As Long = -1)
Dim hl As Hyperlink
For Each hl In ActiveSheet.Hyperlinks
If Len(hl.SubAddress) Then
hl.SubAddress = Replace(hl.SubAddress, sFind, sReplace, lStart, lCount, vbTextCompare)
Else
hl.Address = Replace(hl.Address, sFind, sReplace, lStart, lCount, vbTextCompare)
End If
Next
End Sub
There are a couple of advantages this way.
Firstly, it iterates through just the Hyperlinks collection of the ActiveSheet, as opposed to each of the cells in the UsedRange, which is likely a much smaller collection.
Secondly, it works for both internal and external links, instead of just external.
Regards,
Daniel Ferry
excelhero.com
As a follow-up, it should be noted that neither of our methods will work for hyper links that are in a sheet as a result of the HYPERLINK worksheet function.
Also does not work for Shapes that have hyperlinks….
Wouldn’t a simple global find and replace be more appropriate, since if you want hyperlinks to point to a new location, you want anything to point there as well? Or am I missing something.
JP: a global S&R will unfortunately not work on hyperlinks on objects.
If you really want a catch-all, try my flexfind tool and have it do a find in Objects:
http://www.jkp-ads.com/officemarketplaceff-en.asp
[...] an easy way to fix all hyperlinks at once, with a short macro [...]
The utility from FlexFind (JKP) worked great for me with Office 2K7 http://www.jkp-ads.com/officemarketplaceff-en.asp