Import from access to excel using vba

[shadow12345]

I am trying to import a table called "listed" from my data base called "users one" (stored in "c:\user\my documents\" ) into a sheet in excel.

I would like it to be done using vba in excel if possible. I want the whole table into a worksheet called "Raw" in my active excel sheet.

Is that hard? I found the below from google, but im not sure how to adjust it to fit my needs if that woudl help?

Sub ADOImportFromAccessTable(DBFullName As String, _
    TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
    "TableName", Range("C1")
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=" & _
        DBFullName & ";"
    Set rs = New ADODB.Recordset
    With rs
        ' open the recordset
        .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
        ' all records
        '.Open "SELECT * FROM " & TableName & _
            " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText
        ' filter records
        
        RS2WS rs, TargetRange ' write data from the recordset to the worksheet
        
'        ' optional approach for Excel 2000 or later (RS2WS is not necessary)
'        For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
'            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
'        Next
'        TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

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

 

[xenou]

It's really easy to use a query if you just want a table and that's all:

Excel 2003:
http://www.techrepublic.com/article/use-excels-ms-query-wizard-to-query-access-databases/6112813

Excel 2007/10:
http://www.dummies.com/how-to/content/querying-an-access-database-table-in-excel-2007.html

That's how I'd go. You can use ADO or DAO too of course but with the above method you can get new data as easily as hitting the "refresh" button. More on ADO/DAO (very robust but must be coded by hand):
http://www.erlandsendata.no/english/index.php?t=envbadac