Changing Hyperlink

[Guest]

Hi,

I would like to find and replace the existing Hyperlinks in multiple cells
within a worksheet.

Ex: "D:\Documents and Settings\Satisha\Local Settings\Temp\VAD
4.4\eBusiness_M-Gates.xls" to
"Z:\VAD 4.4\eBusiness_M-Gates.xls"

How to go about doing this.

Regards,
Ravi

 

[Frank Kabel]

Hi
check the following site 8in the middle of the page):
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

 

[Guest]

Hi,

Thanks for your reply.

We used the following function , It searches only for http URLs. But we are
searching for Hyperlinks which is in the format "D:\Documents and Settings\".

Basically the referenced documents are placed in a different network drive.

Fix Hyperlinks (#FixHyperlinks)
Sub Fix192Hyperlinks()
Dim OldStr As String, NewStr As String
OldStr = "http://192.168.15.5/"
NewStr = "http://hank.home.on.ca/"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
Next hyp
End Sub

Please advice

Regards,
Ravi

 

[Dave Peterson]

Just change the oldstr and newstr to what you want:

Oldstr = "D:\Documents and Settings\Satisha\Local Settings\Temp\"
newstr = "Z:\"

and plop that into the code where the http stuff sits.