Tuesday, April 21, 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

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog