CODEWITHSUNDEEP
vbams-accessexcelhyperlink
user2296381
user2296381

Reputation: 197

VBA Access -> Automated Import an Excel Spreadsheet w/ HYPERLINKS

Looking for a wee bit of help here in regards to this. I have a fairly simple Excel datasheet that needs to be input into an Access database in order to be manipulated. However, the data spreadsheet includes a hyperlink. When I try to use my code it gives import errors for the hyperlink field and imports nothing but a blank field.

I am absolutely clueless - can anyone help me on this one? I am trying to use my typical method of importing Excels into Access (my code imports multiple excels at once based on array) - which is below:

DoCmd.TransferSpreadsheet acImport, , ls_tblImport, varFileArray(intCurrentFileNumber, 0) & varFileArray(intCurrentFileNumber, 1), True, "A1:BM" & ls_last_row

Please Note: The hyperlinks I am trying to import are not just URLs, but also Text for the URL. I wish I could just import the Hyperlink text, but sadly that isn't an option.

Upvotes: 0

Views: 15540

Answers (3)

Nexus
Nexus

Reputation: 821

You should implement an import procedure. First create a table with hyperlink field then import your data from Excel into that table.

Option Compare Database

Private Sub Command0_Click()
Dim rec As Recordset
Dim db As Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim xlApp As Object 'Excel.Application
Dim xlWrk As Object 'Excel.Workbook
Dim xlSheet As Object 'Excel.Worksheet


Set xlApp = CreateObject("Excel.Application")
Set xlWrk = xlApp.Workbooks.Open("C:\Users\....\Desktop\EMS Ver3.xlsm") 'Your directory
Set xlSheet = xlWrk.Sheets("SUMMARY") 'your sheet name
Set db = CurrentDb
Set tdf = db.CreateTableDef()
tdf.Name = "My table imported"

'Delete the table if it exists
If TableExists("My table imported") Then
    DoCmd.DeleteObject acTable, "My table imported"
End If

'Create table
Set fld = tdf.CreateField("hyperlinking", dbMemo, 150)
fld.Attributes = dbHyperlinkField + dbVariableField
tdf.Fields.Append fld
' append more field here if you want ...

With db.TableDefs
    .Append tdf
    .Refresh
End With

Set rec = db.OpenRecordset("My table imported")

m = 11 ' Let say your data is staring from cell E11 we will loop over column E until no data is read
Do While xlSheet.Cells(m, 5) <> ""
    rec.AddNew
    rec("hyperlinking") = xlSheet.Cells(m, 5)
    rec.Update
    m = m + 1
Loop
End Sub



Public Function TableExists(TableName As String) As Boolean
Dim strTableNameCheck
On Error GoTo ErrorCode

'try to assign tablename value
strTableNameCheck = CurrentDb.TableDefs(TableName)

'If no error and we get to this line, true
TableExists = True

ExitCode:
    On Error Resume Next
    Exit Function

ErrorCode:
    Select Case Err.Number
        Case 3265  'Item not found in this collection
            TableExists = False
            Resume ExitCode
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "hlfUtils.TableExists"
            'Debug.Print "Error " & Err.number & ": " & Err.Description & "hlfUtils.TableExists"
            Resume ExitCode
    End Select

End Function

The magic is when you create a Memo field and set its attribute to be hyperlink :

Set fld = tdf.CreateField("hyperlinking", dbMemo, 150)
fld.Attributes = dbHyperlinkField + dbVariableField
tdf.Fields.Append fld

You can copy anything from Excel to that field while preserving the hyperlink:

rec("hyperlinking") = xlSheet.Cells(m, 5)

This is just an example. You need to modify your table name , your file directory , your spreadsheet name , your fields name, add more field if you want.

Upvotes: 1

ashareef
ashareef

Reputation: 1846

I don't know how to import hyperlinks using the DoCmd.TransferSpreadsheet as the import function only seems to grab the text for the URL even if the field is a hyperlink instead of text in Access. What I'm going to describe works (tested it) but doesn't seem to be the most direct route.

Write a function in excel (or access and then open the file from access using an excel object) to add another column in your data where the text describing the link and the URL are in the form of text#url#.

From http://www.ozgrid.com/VBA/HyperlinkAddress.htm

Function GetAddress(HyperlinkCell As Range)
    GetAddress = Replace(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

e.g. Google#http://www.google.com/#

Now when you import it will import as a text but then once you change your field type to hyperlink it'll maintain the text and the link to the URL

Upvotes: 0

Andy G
Andy G

Reputation: 19367

If you have direct access to the Excel files you could add a new column to append hash-signs either side of the hyperlink content:

="#"&A1&"#"

Copy this formula down the column, copy and paste-values to remove the formulas. Then re-import to Access.

If you don't have direct access to the files then you could import them into a temporary (empty) table, inserting the hyperlink column into a text-field. Then you could run an Append Query that also modifies this column so that it is suitable to be appended to the hyperlink field.

If, when importing to the temporary table, the column comes across empty then I'm afraid it would require Excel Automation to open the file(s) and insert the hash-signs.

Upvotes: 0

Related Questions