Import data with hyperlinks

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

I have an Excel file that I would like to import into Access. The Excel
file has a column with last names with the email addresses hyperlinked in
the text. I would like to retain this information to Access or have it
split into another column. I have tried to import and cut/paste the data.
Even if I set the data to hyperlink, it manages to loose the hyperlink info
in the import or paste.

Any ideas how I can get all of the data?

Thanks in advance,
Aaron
 
Hi Aaron,

The simplest way I know to do this is to

1) paste this custom VBA function into a module in your Excel workbook's
project.
'Code Starts
Public Function GetURL(R As Range) As Variant
If R.Hyperlinks.Count > 0 Then
GetURL = R.Hyperlinks(1).Address
Else
GetURL = Null
End If
Set C = Nothing
End Function
'Code Ends

2) add a column (headed "Email" or "URL", perhaps) to your worksheet and
fill it with a formula that returns the email address from the
hyperlink, e.g.
=GetURL(G1)
=GetURL(G2)
...

3) Select the column and Copy; then do Edit|Paste Special|Values. This
replaces the GetURL() formulas (which may not import correctly to
Access) with the actual text of the hyperlinks.

4) Import the worksheet to Excel in the usual way.

5) If necessary, change the data type of the resulting Access field to
Hyperlink.
 
Back
Top