Export All Queries of Access Database to Separate Excel Workbook

If you wan to export the access queries to separate excel workbooks

Download the file. Snapshot below -




Here is the code-


Option Compare Database

Private Sub Command2_Click()
Dim fldpth As String
'it will open the dialogbox and choose folder in which all workbooks are saved.
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
Application.FileDialog(msoFileDialogFolderPicker).Show
fldpth = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Me.Text9.Value = fldpth
End Sub

Private Sub Command7_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
If Me.Combo5.Value = ".xlsx" Then

For Each qdf In db.QueryDefs
DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLSX, Me.Text9.Value & qdf.Name & Me.Combo5.Value, False
Next
' when you choose xlsx format
Else
' u choosed xls format
For Each qdf In db.QueryDefs
DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLS, Me.Text9.Value & qdf.Name & Me.Combo5.Value, False
Next
End If
End Sub


Macro File -

http://www.filefactory.com/file/cdcce98/n/Export_all_Access_Queries_To_seperate_Excel_workbook.accdb