Tuesday, 21 April 2015

How to Export MS Access Queries Results into Excel

VBA Macro to Export Microsoft Access Queries Results to an Excel
Option Compare Database
Sub Exp_Query_Data2Excel()
Dim OP_FileName As String

'Capturing Current Database Name

CurDB_Name = Left(CurrentProject.Name, Len(CurrentProject.Name) - 6)

'Defining Output File Name

OP_FileName = CurrentProject.Path & "\" & CurDB_Name & "-" & Format(Date, "DDMMMYYYY") & ".xlsx"

Dim Qry As Object

Dim DB As Object

Set DB = CurrentDb

Set Qry = DB.QueryDefs

'Running all Access Queries and Closing them back.

For Each Qry In DB.QueryDefs
Qry_Name = Qry.Name
DoCmd.OpenQuery Qry_Name
DoCmd.Close acQuery, Qry_Name
Next Qry

'Exporting all Access Queries Results into an Excel Workbook

For Each Qry In DB.QueryDefs
Qry_Name = Qry.Name
DoCmd.TransferSpreadsheet acExport, , Qry_Name, OP_FileName, True
Next Qry
MsgBox "All the Queries Data Successfull Exported", vbOKOnly, "Job Done"
End Sub

Thanks ,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts