我需要帮助。我正在尝试构建要基于日期/时间排序的Access表。我正在从Excel工作表中导出此数据。日期在一个单元格中,但时间在一列中。访问表中的列是日期,时间,储罐和注释。我希望“日期”列看起来像“ mm / dd / yy hhmm”。导出日期时,我希望在循环的每次运行中都包含时间。部分代码片段如下所示:.Fields("Date") = Range("B" & d "and "A" & r").Value
,其中“ A”和r是时间列,其中r是行号,我该如何编程?谢谢。
Sub ExportU1()
Sheets("Plant 1 WP Day").Select
Dim cn As ADODB.Connection, rs As ADODB.Recordset, d, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=U:\Night Sup\Production Report 2003 New Ver 5-28-10_KA.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "UnitOneRouting", cn, adOpenKeyset, adLockOptimistic, adCmdTable
d = 2 'row location of date
r = 13 ' the start of Time, Tank and Comments row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Date") = Range("B" & d).Value
.Fields("Time") = Range("A" & r).Value
.Fields("Tank") = Range("C" & r).Value
.Fields("Comments") = Range("D" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
您是否尝试过如下所示的串联?
.Fields("Date") = Range("B" & d).Value & " " & Range("A" & r).Value
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句