Importing Data from Excel into Access with hyperlinks

[yellow24]

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
Else
ExpandHyperlink = ""
End If
End Function

 

[yellow24]

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

Thanks

 

[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.
basExpandHyperlink.

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