Importing Hyperlinks from Excel to Access

[newbie via AccessMonster.com]

When I import an Excel file containing hyperlinks into Access, the hyperlinks
turn into text and no longer hold their links. I am using the
TransferSpreadsheet function in Access to import my table. I have done a bit
of searching a come across this code:

UPDATE tblTest SET tblTest.hyperdocs = "#" & [hyperdocs] & "#"; (where
tblTest = name of table and hyperdocs = name of hyperlink field in
spreadsheet


This is supposed to add pound signs around the hyperlink to make it active. I
am having trouble getting this to work and want to know if there is a better
way to go about it or if someone could instruct me on how I enter this line
of code into my database. Thanks in advance!

-newbie

 

[Guest]

I had the same problem. I decided to convert the Hyperlink to text in the
Excel file to import into Access. After you import it into Access as a text
field you can change the field from text to Hyperlink and Access does the
conversion for you.

Here is a posting that helped me turn the Hyperlink to a text string:
http://www.ozgrid.com/VBA/HyperlinkAddress.htm

Good Luck!
Judy

 

[John Nurick]

Here is a custom worksheet function which converts an Excel hyperlink
into something Access can import into a text field and then
convert to a hyperlink field.


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

I had the same problem. I decided to convert the Hyperlink to text in the
Excel file to import into Access. After you import it into Access as a text
field you can change the field from text to Hyperlink and Access does the
conversion for you.

Here is a posting that helped me turn the Hyperlink to a text string:
http://www.ozgrid.com/VBA/HyperlinkAddress.htm

Good Luck!
Judy

When I import an Excel file containing hyperlinks into Access, the hyperlinks
turn into text and no longer hold their links. I am using the
TransferSpreadsheet function in Access to import my table. I have done a bit
of searching a come across this code:

UPDATE tblTest SET tblTest.hyperdocs = "#" & [hyperdocs] & "#"; (where
tblTest = name of table and hyperdocs = name of hyperlink field in
spreadsheet


This is supposed to add pound signs around the hyperlink to make it active. I
am having trouble getting this to work and want to know if there is a better
way to go about it or if someone could instruct me on how I enter this line
of code into my database. Thanks in advance!

-newbie

 

[newbie via AccessMonster.com]

Thanks for your help guys! The hyperlinks work now =D

Grazie molto (Thank you very much)

-newbie