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
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.