» 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
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.
The Below VB Code is used in my projects but i did not understand (TargetRange As Range).
Sir, I requested to you please send complite details and
with example project in vb6 code
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:FolderNameDataBaseName.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
I need more information
Shawn Rheal wrote on December 31, 1969 19:00 EST
Why is the RS2WS discluded from this project and how come it isnt commented out. This is confusing me I dont understand why it is coming up not defined with in the VBE. I could you give a simplier code structure to importing data from a Access file. I can do this automatically through the excel import with in the data menu. But I need to create a application that will allow the user to connect to any data base through a application created in the VBA program.
Thanks
Shawn
What about Queries
Brad wrote on December 31, 1969 19:00 EST
The tip states that this code will import data from Access TABELES, but can it pull from QUERIES as well?
Thanks.
What is the used of RS2WS
tats wrote on December 31, 1969 19:00 EST
this is a followup question to shawn , what is the used of that? why when i execute it returns an error (preferably that RS2WS)... is it really be inccluded? or comment it..
TIA.
Will you plz help me out in solving this
Manish wrote on December 31, 1969 19:00 EST
I have inserted an excelsheet as an object in access and want to import data from table in access to excelsheet and vice versa for each field using VB....can you help me out.....
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
Harry Potter and the Order of the Phoenix (Book 5)
