Importing a hyperlink field from excel to Access
Gina
Access 2007. One of the fields being imported is a hyperlink cell in
Excel which links to a specific web page but when I import the data
into the Access field (of hyperlink datatype), I just get the
"display" (text) part of the hyperlink. Whilst on the surface it
looks like a hyperlink, it does not work.
If I edit the hyperlink field In Access, I can see that the "address"
part of the hyperlink is missing. Obviously I don't want to have to
manually edit each hyperlink.
Is there a way to import this hyperlink records from Excel that will
preserve the address part of the link?
Thyanks for any help.
Gina
Patrick Finucane
In Access the format of a hyperlink is "Display Text"#"hyperlink" The
# separates the two,
You can test this out. Create a table and make one of the fields a
hyperlink field. Open up a code module or something like NotePad and
enter a hyperlink
My Junk Site#http://www.junk.com
#http://www.myhyplinksite.com
My Display Site
Now add each line to the table's field. If you clicked on the first 2
examples you would jump to site if it existed. The third example
lacks a hyperlink and you'd go nowhere.
I entered the line for MyJunk in an excel file (not a real Excel
hyperlink, it looks like a text field). When I imported it (not thru
code) using the menu, it let me change the field name and field
types. If I left it as text, I got a text field. If I specified
Hyperlink, I got a hyperlink field
I think you now have some background on Access hyperlinks. I think in
Excel you need to split your hyperlink into 2 parts (columns) and then
concatenate the columns later with the # in Access. I don't know how
one would do this without VBA. Here's some code I used to split and
display the hyperlinks. Change the intRows value and the strWkbName
to your filename.
Public Function ExcelHyperLink()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim varCell As Excel.Range
Dim strWkbName As String
Dim intRow As Integer 'current row
Dim intRows As Integer '# of rows in sheet to process
'supply your filename here.
strWkbName = "C:\YourExcelFolder\YourExcelFile.xlsx"
Set objXL = New Excel.Application
With objXL
.Visible = False
Set objWkb = .Workbooks.Open(strWkbName)
'assumes first sheet is the sheet to use
Set objSht = objWkb.Worksheets(1)
intRows = ReturnLastRow(objSht)
With objXL
'start at row2. Row1 has header. Loop to end. Change
intRows values to 3 or 4 for testing
For intRow = 2 To intRows
'the first column in my file holds the link. The
links start on the 2nd row
'since the first row contains column headings
Set varCell = objSht.Cells(intRow, 1)
MsgBox GetDisplay(varCell) & vbNewLine &
GetAddress(varCell)
Next
End With
End With
objWkb.Close
objXL.Quit
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
End Function
Function GetAddress(HyperlinkCell As Range)
GetAddress = HyperlinkCell.Hyperlinks(1).Address
End Function
Function GetDisplay(HyperlinkCell As Range)
GetDisplay = HyperlinkCell.Hyperlinks(1).Name
End Function
Public Function ReturnLastRow(objSht As Excel.Worksheet)
ReturnLastRow = objSht.Range("A65536").End(xlUp).Row
End Function
Gina
wrote:
>
> - Show quoted text -
Hi Patrick,
Thanks for your response. I understand what you say about the 2
elements of a hyperlink and the need to split the excel hyperlink
column before importing to Access but I dont understand how to use
your Excel code to do this.
My knowledge of Access VB is much better than Excel. I can create the
module within Excel with your code but how do I apply it to the target
worksheet?
Thanks
Gina
Patrick Finucane
That's good. Because I wrote and ran my program from Access.
All you need to change is the workbook name value, the number of rows
to cycle (intRows), and the column containing the hyperlink in the
varCell assignment (I used the first column, thus1 in the "varCells ="
line)
You'd, of course, will want to write back the hyperlink value. To
write to the hyplerlink to col (2) add the following line (assume you
are using a test file)
objSht.Cells(intRow, 2) = GetAddress(varCell)
And you'd want to save the changes before quitting. I think the code
line would be
objWkb.Save
Gina
>
> - Show quoted text -
I inserted your 3 functions code above in my Acces database and made
the changes suggested. However,when I debug it, I get the error
message "Compile error: user defined type not defined". I said my
Access VBA was better that my Excel, but not that much better! Any
ideas?
If we get the code working, I presume I just run the code from within
Access and it will populate a new column (column 2) in the spreadsheet
with the Address part of the hyperlink? Do I follow the same procedure
to get the Display part of the hyperlink? When I copy the 2
"fields" (Display and Hyperlink) to the Access table and then
concatenate them with the # symbol bewtween, do each of the elements
have to be enclosed in double quotes?
Lots of questions! Sorry.
Gina
Patrick Finucane
I wish I could help but I haven't a clue where to program bombs when
you run it.
Maybe you don't have a reference to Excel. (Tools/References). I
did. Add it if it doesn't. Then google for Early and Late binding
with code examples.
Does the code compile? If not, on what line does it bomb,
If it compiles, what line does it bomb on when it is run.
On concatenating the following 2 would work.
"Google#http://www.google.com"
"Google" & "#" & http://www.google.com"
As well as
DIm strDisplayPart As String
Dim strHyperlink As String
strDisplayPart = "Google"
strHyperLink ="http://www.google.com"
YourHyperLinkField = strDisplayPart & "#" & strHyperLink
Create a test table with a hyperlink field and test it out.
