Display link_location instead of friendly name

[sfinx59]

I have 1000, or more hyperlinks of which the link_location only divers
an id-number somthing like
"http://someonessite.com/docs.php?action=info_id=234" The friendly
names divers to a great extent.

When I copy the links to an excel wks the friendly names show. However
I want to select and sort on the link_location id's to see if
something is missing.

Is there a way to display the link_location?

solutions I tried:
- changing format, no result
- right-clic link, > Hyperlink > Copy Hyperlink > move cursor to empty
cell and paste link. This works, however a lot of clics
- tried to create a macro with the same moves, but than excel copies
the friendly name again.

Anybody has a solution

Thanks

Sfinx59

 

[Gary Brown]

The macro below will give you a listing of the friendly name and the link.
HTH,
Gary Brown

'/=============================/
Sub HyperlinkList_Test()
Dim h As Hyperlink
Dim iRow As Long
Dim wksht As Worksheet

On Error Resume Next

Worksheets.Add.Move _
After:=Worksheets(Worksheets.Count)

ActiveSheet.Range("A1").Value = "Worksheet"
ActiveSheet.Range("B1").Value = "Address"
ActiveSheet.Range("C1").Value = "SubAddress"
ActiveSheet.Range("D1").Value = "Name"

Range("A1").Select

For Each wksht In Worksheets
If wksht.Hyperlinks.Count <> 0 Then
For Each h In wksht.Hyperlinks
iRow = iRow + 1
With Selection
.Offset(iRow, 0) = wksht.Name
.Offset(iRow, 1) = h.Address
.Offset(iRow, 2) = h.SubAddress
.Offset(iRow, 3) = h.Name
End With
Next h
End If
Next wksht

Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True

End Sub
'/=============================/