I figured out the code I need to export several queries from Access to an Excel workbook with multiple worksheets, one per query.
I can create the workbook if I open the code and click 'run'.
I want to create a form with a button that when clicked, runs a macro that runs the module to create the workbook.
I would also like the worksheets to be formatted as a table so they are sortable when the Excel file is opened by a user.
I have made several attempts based on Google search results, but still can't figure it out. This is my first attempt at something like this. (I was using Excel to connect to the queries, but that process is very buggy on our system so trying this instead.)
Here is the module which is named 'Export_Results':
Private Sub Command37_Click()
'Exports each of the queries into a multi-tabbed spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Agency_Ave_miles_post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "State_Ave_Miles_post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AveByAgencyByState", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_TotalAgency", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_TotalState", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week1_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week2_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week3_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week4_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week5_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week5_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week7_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week8_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
'Opens the file you just created
Application.FollowHyperlink "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls"
End Sub
I can create the workbook if I open the code and click 'run'.
I want to create a form with a button that when clicked, runs a macro that runs the module to create the workbook.
I would also like the worksheets to be formatted as a table so they are sortable when the Excel file is opened by a user.
I have made several attempts based on Google search results, but still can't figure it out. This is my first attempt at something like this. (I was using Excel to connect to the queries, but that process is very buggy on our system so trying this instead.)
Here is the module which is named 'Export_Results':
Private Sub Command37_Click()
'Exports each of the queries into a multi-tabbed spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Agency_Ave_miles_post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "State_Ave_Miles_post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AveByAgencyByState", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_TotalAgency", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_TotalState", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week1_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week2_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week3_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week4_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week5_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week5_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week7_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week8_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
'Opens the file you just created
Application.FollowHyperlink "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls"
End Sub