convert hyperlink to text

[verdes]

I'm using access 2007.
I have a table with 2 fields:
phtolink - hyperlink
phtopath - text

I am loading the table with existing data from a spreadsheet. The hyperlink field imported correctly into phtolink.

Now I need to convert the hyperlink field to a text field that displays the path to the photo referenced by the hyperlink.

I updated the text field with the hyperlink field but it didn't display the hidden part of the hyperlink.

Help!!! How do I convert a hyperlink to display the path in a text field?

 

[FoundOil]

One way to do this would be to do your conversion in Excel before you bring it into Access. Here is a link that teaches you how to do it:
twigstechtips.blogspot.com/2011/06/excel-easily-convert-hyperlinks-to-url

 

[spikepl]

Or you could use the HyperlinkPart method. Google Access hyperlinkpart

 

[FoundOil]

Right, and depending on how you are updating the table, doing the conversion in Access is probably the better way to go. Here is a possible solution:

1 - Add this code to an existing module or create a new one (this is simpler than what I found online)
2 - Be sure that you set a reference to Microsoft DAO 3.6 (or whichever version you have)

Public Sub GetHyperLinkText()
    Dim db As Database
    Dim rs As Recordset
    
    'Open the table.  Mine has two fields: HyperlinkField, TextField.
    'HyperlinkField contains data and TextField is empty
    'Open the recordset (or table) and then loop through all the records
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblHyperLinks")
    Do Until rs.EOF
        rs.Edit
        rs!TextField = HyperlinkPart(rs!Hyperlinkfield, acFullAddress)
        rs.Update
        rs.MoveNext
    Loop
    
    'clean up
    rs.Close
    db.Close
End Sub