CODEWITHSUNDEEP
sqlexcelms-accessvba
Kish
Kish

Reputation: 89

Import Selected Columns to Excel from Access Table using VBA

I am trying to import selected data from an access table. This table has 4 columns and I want only want columns 2 and 3. In Excel and want them listed in the order: column 3, column 2 (reverse to how they are in Access). Additionally I want to select rows (From Access table) based on a date referenced in the Excel Spread sheet (which I refer to as RpDate in the code). In Access, "Date" is the first column. I need some help please. Thanks.

Sub ADOImportFromAccessTable()
Dim DBFullName As String
Dim TableName As String
Dim TargetRange As Range
Dim RpDate As Range

DBFullName = "C:\Documents\Database.mdb"
TableName = "DataTable"
TargetRange = Range("C5")
RpDate = Range("B2").Value

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        "C:\Documents\Database.mdb" & ";"
    Set rs = New ADODB.Recordset
    With rs
        ' open the recordset
        .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
        ' all records
        .Open "SELECT * FROM " & TableName & _
            " WHERE [Date] = RpDate, cn, , , adCmdText"
        ' filter rows based on date
        rs.Open , TargetRange

    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Upvotes: 0

Views: 3758

Answers (1)

Horaciux
Horaciux

Reputation: 6477

Try this

Sub ADOImportFromAccessTable()
Dim DBFullName As String
Dim TableName As String
Dim TargetRange As Range
Dim RpDate As Range

DBFullName = "C:\Documents\Database.mdb"
TableName = "DataTable"
Set TargetRange = Range("C5")
RpDate = Range("B2").Value

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        "C:\Documents\Database.mdb" & ";"
    Set rs = New ADODB.Recordset
    With rs
        ' open the recordset
        .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
        ' all records
        .Open "SELECT Time, Tank  FROM " & TableName & " WHERE [Date] = " & RpDate & " ORDER BY Tank, Time", cn, , , adCmdText
        ' filter rows based on date            

    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

This isn't prof to SQL injection, but is a start

Upvotes: 1

Related Questions