Import Selected Columns to Excel from Access Table using VBA

Question

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

What is your question? Where do you have trouble? Any error? — Horaciux
– Horaciux, Commented Sep 22, 2014 at 22:51
Also, give us the names of the columns. The solution, I guess, will be like SELECT Column2, Column3 FROM table WHERE [Date] = RpDate ORDER BY Column3, Column2 — Horaciux
– Horaciux, Commented Sep 22, 2014 at 22:55
The column names (in order) are: Date, Time, Tank, Comments. I only want the Tank and Time columns imported into Excel, in the order Tank, Time. — Kish
– Kish, Commented Sep 23, 2014 at 13:32
I am also getting a Compile error: Sub or Function not defined. It is referring to "WHERE" .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable .Open "SELECT Time, Tank * FROM " & TableName WHERE [Date] = "RpDate", "ORDERBY Tank, Time, cn, , , adCmdText" rs.Open , TargetRange — Kish
– Kish, Commented Sep 23, 2014 at 13:35
you need to close quotes right ORDERBY Tank, Time", cn, , , adCmdText — Horaciux
– Horaciux, Commented Sep 23, 2014 at 13:46

Horaciux · Accepted Answer · 2014-09-23 17:14:37Z

1

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

edited Sep 23, 2014 at 17:14

answered Sep 23, 2014 at 13:48

Horaciux

6,5152 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kish Over a year ago

I am getting a Run-time error: Object variable or With block variable not set. It is at TargetRange = Range("C5")

Horaciux Over a year ago

I edited the code, but the error was there before and you didn't mention it, its not related to your question. Just add a Set before assigning an object

Kish Over a year ago

Thanks. I am getting another Run-time error '3705': Ooperation is not allowed when the object is open. With rs .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable .Open "SELECT Time, Tank FROM " & TableName & " WHERE [Date] = " & RpDate & " ORDER BY Tank, Time", cn, , , adCmdText 'The error is in the above line rs.Open , TargetRange End With

Kish Over a year ago

I'm trying to figure how to deal with that last error. I am sure it will need some tuning even after that.

Kish Over a year ago

I have been unable to fix this error, some more help please @Horaciux

|

Collectives™ on Stack Overflow

Import Selected Columns to Excel from Access Table using VBA

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related