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 :-)



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

--
Regards,
Tom Ogilvy


homeseal <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 :-)
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>

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

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"homeseal" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 :-)
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>

Thanx guys, both work great!!!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/