Show hyperlink path (Excel 97)

Topic

New Reply

Is there a way to convert or show the path of a hyperlink in an excel spreadsheet?

For example, at the bottom of these pages, the Privacy Policy is hyperlinked. If I copy the active link to s spreadsheet, is there a formula or setting to where it shows the actual path? In the case of the Privacy Policy, I would need it to show “http://www.wopr.com/html/privacy.shtml” instead of Privacy Policy.

Reply | Quote

Replies

Normally if you right-click on the hyperlink and choose Edit Hyperlink, the dialog box that opens has a place to specify display text and a place with the actual path. Couldn’t you copy the actual path and paste it into the display text using ctrl+c and ctrl+v?

Reply | Quote

In VBA you can return the actula text of the URL.

HyperlinkUrl = ActiveSheet.Hyperlinks(“Privacy Policy.”).Address

You could create a UDF as follows

Function ShowUrl(rng As String)
    ShowUrl = ActiveSheet.Hyperlinks(rng).Address
End Function

So if the Privacy Policy. link was in A1, use

ShowUrl (A1)

to return

http://www.wopr.com/html/privacy.shtml

Andrew C

Reply | Quote

Andrew,
How do I set up a user defined function?

I created a new module in my personal.xls and copied exactly what you have typed below.

I run the UDF and select the “ShowURL” function and I receive a value error:

I also had an error of “type mismatch”.

What am I doing wrong?

Egghead,
I can do as you suggest, but I have 400 hyperlinks per day that I need to do this to. It can become quite tedious.

Reply | Quote

Sounds like you really just want a macro that lists all of the hyperlinks on a sheet. See below. HTH –Sam

Option Explicit

Sub ListHyperlinks()
Dim cs As Worksheet, ws As Worksheet
Dim h As Hyperlink, i As Long
    Set cs = ActiveSheet
    Set ws = ActiveWorkbook.Worksheets.Add
    ws.Cells(1, 1) = "Hyperlinks on " & cs.Name
    ws.Cells(2, 1) = "Range"
    ws.Cells(2, 2) = "Display"
    ws.Cells(2, 3) = "Address"
    i = 3
    For Each h In cs.Hyperlinks
        ws.Cells(i, 1) = h.Range.AddressLocal
        ws.Cells(i, 2) = h.TextToDisplay
        If h.Address  "" Then
            ws.Cells(i, 3) = h.Address
        Else
            ws.Cells(i, 3) = h.SubAddress
        End If
        i = i + 1
    Next h
    ws.Cells(1, 4).Select
End Sub

Reply | Quote

I’m not sure what you mean by Run UDF. However the code dies not tseem to be functional in XL 97. The following macro should convert all such links in the active sheet to the actual urls.

Sub ShowUrls()
Dim hl As Hyperlink
For Each hl In ActiveSheet.Hyperlinks
hl.Range = hl.Address
Next
End Sub

Andrew C

Reply | Quote

Maybe I have my acronyms confused. I thought UDF was User Defined Function.
When I tried to run that function, those were the errors I received.

However, the macro you provided me does exactly what I need it to do.

Thanks a bunch!!!

Reply | Quote