M
Mike Archer
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
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