Changing multiple hyperlinks

[homeseal]

Hi anybody!

I'm trying to change multiple hyperlinks in Excel. What I have are a
bunch of hyperlinks which point anywhere, for example:

afolder/folder1/folder3/2003.doc
or
bfolder/folder2/folder3/2003.doc

so basically the only thing in common with all these hyperlinks is that
they all point to a 2003.doc in a folder. What I need to do is change
the document name to 2004. Please tell me how I can do this! I tried
a few things but all either change the first part of a directory or
only http:// hyperlinks. Is there a way to change the hyperlinks so
that if a macro finds "2003.doc" in any part of a hyperlink, it will
change it to 2004.doc?

Puleez help me out guyz!!

Thanx!

homeseal

 

[Harald Staff]

Hi

See if this works for you (backup first and don't save afterwards unless you've tested
that it worked fine):

Sub ChangeLinks()
Dim HL As Hyperlink
Dim sOld As String, sNew As String
sOld = InputBox("Replace what:")
If sOld = "" Then Exit Sub
sNew = InputBox("Replace " & sOld & " with:")
If sNew = "" Then Exit Sub
For Each HL In ActiveSheet.Hyperlinks
HL.Address = Replace(HL.Address, sOld, sNew)
HL.TextToDisplay = Replace(HL.TextToDisplay, sOld, sNew)
Next
End Sub

 

[homeseal]

Thanx guys, both work great!!!