Excel 2003
--
May your footsteps be lost in the music of the forest.

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.
--
Gary''s Student - gsnu200776


"Ale" wrote:

Excel 2003
--
May your footsteps be lost in the music of the forest.

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"
--
May your footsteps be lost in the music of the forest.


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200776


"Ale" wrote:

Excel 2003
--
May your footsteps be lost in the music of the forest.

Show me a typical hyperlink you started with.
--
Gary''s Student - gsnu200776


"Ale" wrote:

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"
--
May your footsteps be lost in the music of the forest.


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200776


"Ale" wrote:

Excel 2003
--
May your footsteps be lost in the music of the forest.

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



--
May your footsteps be lost in the music of the forest.


"Gary''s Student" wrote:

Show me a typical hyperlink you started with.
--
Gary''s Student - gsnu200776


"Ale" wrote:

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"
--
May your footsteps be lost in the music of the forest.


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200776


"Ale" wrote:

Excel 2003
--
May your footsteps be lost in the music of the forest.

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" wrote:

Excel 2003
--
May your footsteps be lost in the music of the forest.

Hutch,

Thanks a ton.

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


--
May your footsteps be lost in the music of the forest.


"Tom Hutchins" wrote:

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" wrote:

Excel 2003
--
May your footsteps be lost in the music of the forest.

Hutch,

Just tried it on multiple highlighted hyperlinks; the macro changed all at
once. Wow!
--
May your footsteps be lost in the music of the forest.


"Tom Hutchins" wrote:

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" wrote:

Excel 2003
--
May your footsteps be lost in the music of the forest.