macro/vba for a range of hyperlinks

[Guest]

Hello
I'm developing a library of commodity codes each entry will have a hyperlink
to the vendor cut sheet.

I need to redirect all the hyperlinks to another directory, besides going to
each hyperlink and re-educating it - I'm hoping someone can help me with a
macro or vba code.

cheers,
jkb

 

[Ron de Bruin]

hi jkb66

Try this one

Sub tester()
Dim hlink As Hyperlink
For Each hlink In ActiveSheet.Hyperlinks
If InStr(hlink.Address, "\Data") Then
hlink.Address = Application.Substitute( _
hlink.Address, "\Data", "\Data2")
End If
Next
End Sub

 

[Guest]

Thanx Ron

Unfortuately the original addresses for each of the cut sheets are all in
different directories, in an effort to make this a portable list- I've copied
all the cut sheets to reside in the file location with the excel spreadsheet.

here's an example of an orginal address (I'm not responsible for the file
path!)

\\SLI0014\_017191$\4000 Eng\4700 Elect\47EX Engineering\47ES Material or
Equipment Specification\Material Cut Sheets\Power\Power Cables &
Wire\7847-PC010.PDF
but now all I need the file path to say is 7847-PC010.PDF!

I would appreciate any ideas.

jkb

 

[Ron de Bruin]

Try this one

Sub tester2()
Dim hlink As Hyperlink
For Each hlink In ActiveSheet.Hyperlinks
hlink.Address = Mid(hlink.Address, InStrRev(hlink.Address, "\") + 1, 255)
Next
End Sub

 

[Guest]

Excellent thanx Ron! that worked very well - have a great day!

jkb