Map excel Hyperlink data (URL) into an Access Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, How can I capture a list of URL's from excel into a table in Access ???

I have a table with a field of type Hyperlink... however, when I map the data from excel it displays as a hyperlink field, but, there is no address associated with it when I attempt to edit it from the properties ->edit hyperlink.

Any suggestions

LD
 
Hi LD,

Paste the ExpandHyperlink() function at the end of this post into a
module in Excel. Then add a column to the Excel table and use
ExpandHyperlink() in formulas to display the entire contents of the
hyperlink.

Access will import this as a Text field, but you can then change it to a
Hyperlink field.

Hi, How can I capture a list of URL's from excel into a table in Access ???

I have a table with a field of type Hyperlink... however, when I map the data from excel it displays as a hyperlink field, but, there is no address associated with it when I attempt to edit it from the properties ->edit hyperlink..

Any suggestions?
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
 
Thanks John, I'll give it a shot...
LD

----- John Nurick wrote: -----

Hi LD,

Paste the ExpandHyperlink() function at the end of this post into a
module in Excel. Then add a column to the Excel table and use
ExpandHyperlink() in formulas to display the entire contents of the
hyperlink.

Access will import this as a Text field, but you can then change it to a
Hyperlink field.

Hi, How can I capture a list of URL's from excel into a table in Access ???
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
 
Back
Top