Import data from Excel with Hyperlink column, into Access?

Question

I have followed the import directions (very simple indeed) and the excel hyperlink column imports into Access as a hyperlink field, but the URL address is missing in the Access field? The text is there but when you right click and look to edit the hyperlink, there is no URL target address shown! It is blank! Clicking on the hyperlink field does nothing! I am creating a new table in Access, not appending to an existing table. Any help will be appreciated.

I can't find any current posts about this?

Answer 1

Directly importing hyperlinks from Excel into Access only works if the display text of the hyperlinked cells is the same as the hyperlink address (the URL).

As a workaround, you can export the Excel sheet to HTML and import the HTML file into Access.

Answer 2

Let's say your hyperlink field is named MyField.

The following expression will return the display text, if different from the hyperlink address (URL):

Left([MyField],InStr([MyField],"#")-1)

And for the hyperlink address:

Mid([Hyp],InStr([MyField],"#")+1,InStrRev([MyField],"#")-InStr([MyField],"#")-1)

Mid([MyField],InStr([MyField],"#")+1,InStrRev([MyField],"#")-InStr([MyField],"#")-1)

Finally, for the subaddress (if any):

Mid([MyField],InStrRev([MyField],"#")+1)

Answer 3

Thanks again, and I will report this bug!

Also, do you know how I would now create 2 new text fields in the same table, one for the text of the hyperlink, and one for the target URL in text format.

I guess an update query, but with what expression?

Answer 4

You can report it through File > Feedback > Send a Frown, or put in a request at https://access.uservoice.com/

Answer 5

I just tried it and it worked fine! Thanks!

Do you have any idea why Microsoft does not fix this bug?

It seems as if it has not been working for years, based on some of the old posts!