Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hyperlink to access database from excel
Hello. I am adding a record to an access database with an excel macro. In
the database, I have a field that is set as hyperlink for data type. When i type a path into the field it assigns a the text as the text to display and as the address. When I add the path using VBA in excel, the string is only text to display, and address is left empty. End result, it looks like a hyperlink but it is only displayed text. I pasted my code below. The 3 key lines are commented in all caps. Dim LinkName As String 'DECLARE VARIABLE FOR LINK Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long LinkName = ThisWorkbook.Path & Application.PathSeparator & "swimlanes" & Application.PathSeparator & ThisWorkbook.Worksheets("Time Units").Range("D1").Value 'ASSIGN THE VALUE FOR LINK (PATH OF FILE) ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=\\cessna3\commonreports\SE Flight\SwimlaneData.mdb" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Swimlanedata", cn, adOpenKeyset, adLockOptimistic, adCmdTable With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Date") = Format(Now(), "mm/dd/yy") .Fields("Time") = Format(Now(), "h:mm AMPM") .Fields("Badge") = Application.UserName .Fields("Unit") = Label6.Caption .Fields("Position") = Mid(ThisWorkbook.Name, 1, InStr(ThisWorkbook.Name, ".xls") - 1) .Fields("Work Item") = Label2.Caption .Fields("Planned End Time") = Label4.Caption .Fields("Planned Time Units") = Val(PlannedTimeUnits) .Fields("Actual Time Units") = Val(TextBox1.Text) .Fields("Delta") = Val(PlannedTimeUnits) - Val(TextBox1.Text) .Fields("Reason Code") = ComboBox1.Text .Fields("Swimlane File") = LinkName 'ADD THE HYPERLINK TO THE DATABASE .Update ' stores the new record End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing -- Thanks, Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hyperlink to access database from excel
A hyperlink has the form
displaytext#address#subaddress#screentip In your case I think you need .Fields("Swimlane File") = LinkName & "#" & LinkName "Mike Archer" wrote in message ... Hello. I am adding a record to an access database with an excel macro. In the database, I have a field that is set as hyperlink for data type. When i type a path into the field it assigns a the text as the text to display and as the address. When I add the path using VBA in excel, the string is only text to display, and address is left empty. End result, it looks like a hyperlink but it is only displayed text. I pasted my code below. The 3 key lines are commented in all caps. Dim LinkName As String 'DECLARE VARIABLE FOR LINK Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long LinkName = ThisWorkbook.Path & Application.PathSeparator & "swimlanes" & Application.PathSeparator & ThisWorkbook.Worksheets("Time Units").Range("D1").Value 'ASSIGN THE VALUE FOR LINK (PATH OF FILE) ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=\\cessna3\commonreports\SE Flight\SwimlaneData.mdb" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Swimlanedata", cn, adOpenKeyset, adLockOptimistic, adCmdTable With rs .AddNew ' create a new record ' add values to each field in the record .Fields("Date") = Format(Now(), "mm/dd/yy") .Fields("Time") = Format(Now(), "h:mm AMPM") .Fields("Badge") = Application.UserName .Fields("Unit") = Label6.Caption .Fields("Position") = Mid(ThisWorkbook.Name, 1, InStr(ThisWorkbook.Name, ".xls") - 1) .Fields("Work Item") = Label2.Caption .Fields("Planned End Time") = Label4.Caption .Fields("Planned Time Units") = Val(PlannedTimeUnits) .Fields("Actual Time Units") = Val(TextBox1.Text) .Fields("Delta") = Val(PlannedTimeUnits) - Val(TextBox1.Text) .Fields("Reason Code") = ComboBox1.Text .Fields("Swimlane File") = LinkName 'ADD THE HYPERLINK TO THE DATABASE .Update ' stores the new record End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing -- Thanks, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
Retrieving hyperlink from MS Access database | Excel Programming | |||
Using Excel as a database and need macro or vba to take data entered on one tab and update the database by adding to the next avail row | Excel Programming | |||
Importing Data from an Access Database Including a Hyperlink Colum | Excel Discussion (Misc queries) | |||
Adding data from Excel sheet to Access database | Excel Programming |