How do I create a macro to remove path from hyperlink "address"?

[Ale]

Excel 2003

 

[Gary''s Student]

Hi Ale:

It will depend on the form of the hyperlink. For example, if the hyperlinks
look like:

=HYPERLINK("file:///C:\Documents and
Settings\User\Desktop\Book2.xls#Sheet2!B9")

Then a macro like:

Sub path_killer()
For Each r In Selection
v = r.Formula
s = Split(v, "\")
u = UBound(s)
r.Formula = Left(s(0), (Len(s(0)) - 2)) & s(u)
Next
End Sub


will change to the hyperlink to:

=HYPERLINK("file:///Book2.xls#Sheet2!B9")

As you see, the path has been removed.

 

[Ale]

Thanks Gary"s Student.

When I ran the macro I copied from your post, it added to the "Text to
display" inst ead of removing the path from the "Address"

 

[Gary''s Student]

Show me a typical hyperlink you started with.

 

[Ale]

2007 10 22 V&M 0.jpg
Above is "Text to Display"

Below is hyperlink "Address"
N:\2007 Tax Deductibles\2007 10 22 V&M 0.jpg

The result I want is for the path to be removed so that
the hyperlink "Address" is
2007%2010%2022%20V&M%200.jpg
-- the hyperlink address without file path.

Thanks

 

[Tom Hutchins]

Try this in a VBA module:

Sub RemoveAddr()
Dim x As Integer, r As Range
On Error Resume Next
For Each r In Selection
x = InStrRev(r.Hyperlinks(1).Address, "\")
If x > 0 Then
r.Hyperlinks(1).Address = Right(r.Hyperlinks(1).Address, _
Len(r.Hyperlinks(1).Address) - x)
End If
Next r
End Sub

Hope this helps,

Hutch

 

[Ale]

Hutch,

Thanks a ton.

I linked you code to a command button "Path_Killer" and it works like a
charm. Sweet!

 

[Ale]

Hutch,

Just tried it on multiple highlighted hyperlinks; the macro changed all at
once. Wow!