Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show hyperlink path (Excel 97)

    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.

  2. #2
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show hyperlink path (Excel 97)

    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?
    egghead

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Show hyperlink path (Excel 97)

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

    HyperlinkUrl = ActiveSheet.Hyperlinks("Privacy Policy.").Address

    You could create a UDF as follows<pre>Function ShowUrl(rng As String)
    ShowUrl = ActiveSheet.Hyperlinks(rng).Address
    End Function</pre>

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

    ShowUrl (A1)

    to return

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

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show hyperlink path (Excel 97)

    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.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show hyperlink path (Excel 97)

    Sounds like you really just want a macro that lists all of the hyperlinks on a sheet. See below. HTH --Sam
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Show hyperlink path (Excel 97)

    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

  7. #7
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show hyperlink path (Excel 97)

    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!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •