Wednesday, 13 May 2015

Access VBA DAO CopyFromRecordset Method to Copy Selected Fields Data from Access Table to Excel

Access VBA DAO CopyFromRecordset Method to Copy Selected Fields or SQLQuery Results Data from Access Table to Excel

How to Transfer Selected Fields/SQL Query Result data from an Access DAO Recordset to Excel with VBA Macro
'Using ADO Method to Import data from an Access Database Table to an Excel worksheet, we need to use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.
'By clicking Tools-References in VBA, and then choose an appropriate version of Microsoft ActiveX Data Objects x.x Library from the list, as shown below :





The following Macro has written in Excel VBA Module to Import/Copy Data from a Selected Fields/SQL Query results from Access Database Table into an Excel Sheet using DAO Recordset Method.
'-------------------------------------------------
Sub DAO_Access_Slected_2_Excel()
Dim MyPath As String, DBName As String, MyDB As String, StrSQL As String
Dim J As Long, K As Long, FieldCount As Long

Dim DAO_DB As DAO.Database
Dim DAO_RecSet As DAO.Recordset

Dim WS As Worksheet
Dim Rng As Range

DBName = "Sales_Database.accdb"
MyPath = ThisWorkbook.Path
MyDB = MyPath & "\" & DBName
My_Table = "Sales_Table"

'Assigning the Database Reference to an object variable:
Set DAO_DB = DBEngine.Workspaces(0).OpenDatabase(MyDB)

Set WS = ActiveWorkbook.Sheets("Data")
WS.Activate
WS.Range("A1").Select

'Opening a Recordset to Copy Specific Fields/SQL Query Result Data from a Access Table named "Sales_Table" :

Str_SQL = "SELECT Sales_Period, Prod_Id, NetSales From SALES_TABLE Where NetSales >500"

Set DAO_RecSet = DAO_DB.OpenRecordset(Str_SQL, dbOpenDynaset)

Set Rng = WS.Range("A1")

FieldCount = DAO_RecSet.Fields.Count

For J = 0 To FieldCount - 1
'Copy Column Names to First Row of the Worksheet:
Rng.Offset(0, J).Value = DAO_RecSet.Fields(J).Name
DAO_RecSet.MoveFirst

'Copying Records from Recordset and Pasting from Second Row of the Worksheet:
K = 1

Do While Not DAO_RecSet.EOF
Rng.Offset(K, J).Value = DAO_RecSet.Fields(J).Value
DAO_RecSet.MoveNext
K = K + 1
Loop

Next J

'Selecting a All Columns in Data range to Auto Filt:
Range(WS.Columns(1), WS.Columns(FieldCount)).AutoFit
'Selecting a All Columns in Data range to Delete:
'Range(WS.Columns(1), WS.Columns(FieldCount)).Delete

'Closing the RecordSet and DB
DAO_RecSet.Close
DAO_DB.Close

MsgBox "All the Records Copied from Target Access Table To Excel Sheet", vbOKCancel, "JobDone"

'Destroying the Variables
Set ADO_RecSet = Nothing
Set DAO_DB = Nothing

End Sub

Thanks,
TAMATAM
          [ BI-Reporting Analyst ]

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts