CODEWITHSUNDEEP
excelms-accessvba
Kish
Kish

Reputation: 89

Combine two columns in Excel and export to Access as one column using VBA

I need some help. I am trying to build an Access table that I want to have sorted based on Date/Time. I am exporting this data from an Excel worksheet. The date is in one cell, but the times are in a column. The columns in the Access Table are Date, Time, Tank and Comments. I want the Date column to look like "mm/dd/yy hhmm". When exporting Date, I want to include the Time in each run of the loop. A part of the code snippet would look like: .Fields("Date") = Range("B" & d "and "A" & r").Value, where "A" & r is the time column, where r is the row number, how might I program this? Thanks. 

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

Upvotes: 0

Views: 517

Answers (1)

Kokkie
Kokkie

Reputation: 556

Have you tried a concatenation like below?

.Fields("Date") = Range("B" & d).Value & " " & Range("A" & r).Value

Upvotes: 1

Related Questions