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

  • Thread starter Thread starter Harley
  • Start date Start date
H

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?
 
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?
Click to expand...


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
 
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
 
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.
 
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
 
Back
Top