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

 

[Tom Ogilvy]

With a macro

Sub changeLinks()
Dim hlink as Hyperlink
for each hlink in ActiveSheet.hyperlinks
hlink.Address = Application.substitute(hlink.Address,"2003","2004")
Next

end sub

 

[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!!!

 

[Jasp]

how do you limit it to change only the cells selected?

 

[DAC8190]

Thanks for this example; it enables me to repair my hyperlinks when Excel decides to break them, which seems to happens far too frequently. Every time I think I have the relative references set Excel has a habit of changing my hyperlinks from local relative references to a fixed reference as described below.

For example:
The relative link that works and what it should be and stay is:
09MovieTitles\Frequency.doc

At some point Excel (or Microsoft) changes it to a fixed reference as shown here:
C:\Users\CMAa\AppData\Roaming\Microsoft\Excel\09MovieTitles\Frequency.doc

With this example macro I can repair the reference and not start over! Thanks!

 

[strotzyl]

Thanks a million for this. You saved me hours of work.