Can VBA edit hyperlink(s) address

D

DougM

I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to
U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at
it, I can take an unneccessary folder (....../folder/....) out of the
address. I tried writing VBA to do this without success. Suggestions? Thank
you in advance. Doug
 
D

DougM

This is helpful but I think it doesn't solve my problem. Thank you.

It looks like this solution replaces old with new. I need to edit each hyp
the same way (ie c:\target\folder\folder\filename to
u:\target\folder\filename) because each hyp goes to a different file.

Thanks again and in advance. Doug
 
D

DougM

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
 
Top