Importing Data from Excel into Access with hyperlinks



Is there an easy way to import Hyperlinks that are created in Excel into
Access? I am creating an Access database instead of using Excel - there are
several hundred hyperlinks in the excel spreadsheet. When I import the
spreadsheets, the link does not import properly. I have to edit every single
hyperlink. Please tell me there is an easier way to do this!

Thank you

John Nurick

I use this little custom Excel worksheet function to convert the
hyperlinks into text strings that match the format Access uses. These
import nicely into an Access text field; if you change that into a
hyperlink field the strings convert into hyperlinks.

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.
' Paste this function into an Excel module, then
'add a column to the worksheet and use the function
'in a formula to convert the hyperlinks into text.

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


When you say "Paste this function into an Excel module" are you referring to
a macro? Or just a cell within excel? I'm kind of confused...........


John Nurick

It's not strictly speaking a macro, but yes, you need to go to the VBA
editor, insert a module (ordinary one, not a class module) and paste
the code into the module.

NB: 1) Before you do it, go to Tools|Options in the VBA editor and
make sure that the Require Variable Declaration option is turned on.

2) The name of the module must be different from the name of the
function. Often people prefix the module name with "bas", e.g.

Once you've put the functoin in the module you can use it in formulas
on the worksheet.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question