Import data from Access to Excel (ADO) using VBA in Microsoft Excel

With the procedure below you can import data from an Access table to a worksheet.

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

The macro examples assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft
ActiveX Data Objects x.x Object Library.
Use ADO if you can choose between ADO and DAO for data import or export.

Elis
August 26, 2013 at 10:25 am

"Hi Peter,
I think this is a 'feature' of Excel!
You could try the following - I haven't tried them myself though, they're just ideas:
1) Append a null expression to your formulae containing a volatile function. Excel might then be forced to recalculate and thus refresh your data.
For example, if the formula pulls in a number, multiply it by (NOW()/NOW()) which always equals '1' and thus has no effect.
For a text field, append something like:
&IF(Now()=1,"""","""") which always adds nothing to the string.
2) Force a full re-calc (Shift-F9) either manually or by VBA event driven code.
Perhaps someone else can offer other ideas?
Alan."

Reply ↓
David
August 26, 2013 at 10:25 am

I am using index and match in a spreadsheet to retrieve data from a file extracted from access and then opened as an excel worksheet. The index and match will only retrieve the data from the excel sheet only if I use F2 and push enter on the refererence cell in the excel sheet created from access. I have tried all combinations of number format, copy paste...with no luck.

Reply ↓
Daniel
August 26, 2013 at 10:24 am

"Hi, I have an access file that needs to be imported to Excel in Microsoft Office 2000. I saw your code and wanted to try it out. However, the compiler says that RS2WS is not defined.
Can you please tell me why and what the RS2WS is doing? Thanks "

Reply ↓