How to import an excel hyperlink column into an access table?

[Harley]

I am trying to import an excel spreadsheet with columns of hyperlinks into a
table in access. When I import the spreadsheet it looses all functions and
formulas. Any ideas?

 

[John Nurick]

Hi Harley,

1) Access tables don't and can't contain formulas, only data.

2) Irritatingly, Excel hyperlinks don't seem to import properly. At
the end of this messages I've posted a little Excel custom worksheet
function which converts the contents of an Excel hyperlink cell into a
string that does the same job in an Access hyperlink field.

Add a column to the worksheet and use ExpandHyperlink() formulas in
it. Then either import the result to a hyperlink field in an existing
table or import to a new table. If the latter, the hyperlinks will be
in a text or memo field; convert it to a hyperlink field.

I am trying to import an excel spreadsheet with columns of hyperlinks into a
table in access. When I import the spreadsheet it looses all functions and
formulas. Any ideas?

Public Function ExpandHyperlink(R As Range, _
Optional AddressOnly As Boolean = False) As Variant

'Converts Excel hyperlink into a string that can be
'imported into an Access text field which can then
'be converted into a hyperlink field.

If R.Hyperlinks.Count > 0 Then
With R.Hyperlinks(1)
ExpandHyperlink = IIf(AddressOnly, .Address, _
.Name & "#" & .Address & "#" & .SubAddress)
End With
Else
ExpandHyperlink = ""
End If
End Function

 

[Dale Fye]

John,

Do you actually use the hyperlink data type in your tables? I've found that
they are more of a headache than they are worth, and generally just store the
links as text (or memo for really long links) fields. Then, in my forms, I
will execute a application.followhyperlink method and pass it the string
(doing this in either the doubleclick event of the textbox or the click event
of a command button.

What is the advantage of using this data type?

Dale

 

[John Nurick]

Hi Dale,

I use it now and then. The advantages seem to be:

* Like a hyperlink in HTML, Word or Excel you can hide the URL and
display whatever you want. The HyperLinkPart() function lets you deal
with the individual pieces.

* If you drag a file, shortcut or internet shortcur onto a textbox
bound to a hyperlink field, Access automatically creates the
corresponding hyperlink (this is really handy if you want to make it
easy for users to create hyperlinks).

* Er...

* That's it.

 

[Bas]

Hi All,

My problem was kind of similar: Pasting a column of hyperlinks into ACCESS 2007. The problem was that once I pasted them, they did not respond on a click and did not send me to the website.

The problem is: ACCESS 2007 reacts to hyperlinks if they are manually inserted into the fields. (so actually typing)
The solution: Instead of retyping all web-addresses I simply used the search (ctrl F) and found and replaced all the dots "." with the same dots. -> Replace all "." by "."

This somehow fixed the links and I did not have to work with any codes or other tricks.

Hope it helps!

Kind regards,
Bas