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

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions

CATEGORY - Import and Export in VBA VERSION - All Microsoft Excel Versions

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.

Book Store:

VIEW ALL BOOKS

Recommended Books:

Email Tip

Print Tip

Add to My Tips

Rate This Tip



1	2	3	4	5

Rating: 3.06 Views: 369637

RS2WS

Crystal Posted on: 31-12-1969

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

ACCESS DATA TO EXCEL

Peter Holohan Posted on: 31-12-1969

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: ACCESS DATA TO EXCEL - Peter Holohan from MELBOURNE AUSTRALIA wrote on September 3, 2003 8:26 AM EST

Alan Posted on: 31-12-1969

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.