» 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
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:
Recommended Books:
- The Essential 55: An Award-Winning Educator's Rules for Discovering the Successful Student in Every Child
- How to Read A Financial Report
- Microsoft Office Xp: Advanced Concepts and Techniques: Word 2002, Excel 2002, Access 2002, Powerpoint 2002
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- Learn MS Excel 2002 VBA/XML Programming
- The Intelligent Investor: The Definitive Book On Value Investing, Revised Edition
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.



Can you please tell me why and what the RS2WS is doing? Thanks