Replace Hyperlinks

[Guest]

Hi,

I've a spreadsheet (Excel Program of Microsoft Office XP) which has hundreds
of hyperlinks established from different CELLS to the FILES that are stored
on a drive.
For e.g. Cell A1 has a hyperlink address as

"D:\DOCUME~1\Satisha\LOCALS~1\Temp\Approach\Project_Planning_Tracking_and_Managing_Process.doc"

Which I want REPLACE with

"Z:\Approach\Project_Planning_Tracking_and_Managing_Process.doc"

Please note that I want to replace part of the hyperlink address with the
new path.(e.g. "D:\DOCUME~1\Satisha\LOCALS~1\Temp" with "Z:\").

I couldn't fix it using the Excel Find/Replace option as it searches only
"http" urls and doesn't search for any other url as listed above.

Please let me emphasize that there are hundreds of such cells which needs to
fixed and I'm really requesting you to provide a detailed answer as quickly
as possible.

Appreciate your speedy solution to this issue.

Please let me know if you need anything else.

Thanks,
Satish

 

[Andy Brown]

"D:\DOCUME~1\Satisha\LOCALS~1\Temp\Approach\Project_Planning_Tracking_and_Ma
naging_Process.doc"

Which I want REPLACE with

"Z:\Approach\Project_Planning_Tracking_and_Managing_Process.doc"

Did you try (Replace) *\Approach (With) Z:\Approach (?)

HTH,
Andy

 

[Guest]

Andy,

Thanks for your quick response. Appreciate it.

Unfortunately, this option doesn't work. This option searches for the given
text in the formulas but not in the hyperlink address.

Please advise.

Thanks,
Satish

 

[Andy Brown]

Unfortunately, this option doesn't work. This option searches for the
given

text in the formulas but not in the hyperlink address.

What can I say? ; worked for me. Maybe something to do with how they were
created?

You could try converting with a macro, eg:

ActiveCell = "Z:\" & _
Right(ActiveCell, Len(ActiveCell) - InStr(1, ActiveCell, "Approach") + 1)

, which (should) handle a selected link. But as you say, you have 100s.
Hopefully someone else will pitch in.

Rgds,
Andy

 

[Dave Peterson]

I think that Satisha_SB used Insert|hyperlink to add the hyperlinks.

He had one suggestion at another post, but I guess it didn't work for him.

 

[Guest]

Dave,

Yes. He didn't work either. The script you suggested could search for those
hyperlinks who had "http" hyperlink addresses.

Does it help if I send my spreadsheet? If yes, how could I send it to you?

Looking forward to your reply..

Thanks,
Satish

 

[Dave Peterson]

At that other thread, this was my follow up suggestion:


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.

So the subroutine would change from:

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

to:

Fix Hyperlinks (#FixHyperlinks)
Sub Fix192Hyperlinks()
Dim OldStr As String, NewStr As String
Oldstr = "D:\Documents and Settings\Satisha\Local Settings\Temp\"
newstr = "Z:\"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
Next hyp
End Sub

It was written for xl2k and higher.

If you use xl97, then change this line:
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
to
hyp.Address = application.substitute(hyp.Address, OldStr, NewStr)