How can I import Excel hyperlinks into Access hyperlink field?

[Guest]

When I import hyperlinks from Excel into a hyperlink field in Access, the
links are "dead". The URL appears, and it looks like a hyperlink (blue,
underlined), but when you click it nothing happens. Apparently it brings in
the URL as text, and does not set the URL property. Is there a way to import
Excel hyperlinks so that they are functional in Access?

 

[John Nurick]

Here's how I do it:

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