edit hyperlink code cont from 12/8/04

[Guest]

I tried running this without success. Can anybody tell me why?

Sub Fix192Hyperlinks()
'
Dim OldStr As String, NewStr As String
OldStr = "c:\My Templates\Profile Database\"
NewStr = "\\Pinoak\Data\Mfr\Grinding Room\"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
Next hyp
End Sub

Thank you

 

[Ron de Bruin]

Do you use Excel 97 Doug ?

Try
hyp.Address = Application.WorksheetFunction.Substitute(hyp.Address, OldStr, NewStr)

 

[Guest]

excel 2003

Do you use Excel 97 Doug ?

Try
hyp.Address = Application.WorksheetFunction.Substitute(hyp.Address, OldStr, NewStr)

 

[Ron de Bruin]

Hi Doug

Send me a workbook with a few links (private)and i take a look at it

 

[Dave Peterson]

Are you sure your activesheet had hyperlinks that matched your OldStr?

Maybe replacing this:
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
with:
hyp.Address = Replace(hyp.Address, OldStr, NewStr, 1, -1, vbTextCompare)

will help.

If you don't specify it (like vbTextCompare), replace is case sensitive.

Check VBA's help for Replace for more info.