Question How do I export selected queries to separate excel files

[a497253]

I am using MS Access 2003/2007.

I understand that there is an Output To macro that will export selected queries into an Excel (xls, xlsx) format to a specific path that can be converted to vba:

DoCmd.OutputTo acOutputQuery, "QueryName", "ExcelWorkbook(*.xlsx)", "C:\Users\Default\Desktop\ReportName.xlsx", False, "", , acExportQualityPrint

If I have a user defined table or query that stores query names, user friendly export names, and export paths how would I be able to export a list of queries from a table list into separate Excel files to specific folder paths with the user friendly names?

Here is an example of what the table would look like:

Query            User Friendly Name            Export Path
Query1            Report 1                      C:\Users\Default\Path1\
Query2            Report 2                      C:\Users\Default\Path2\
Query3            Report 3                      C:\Users\Default\Path3\

I'm aiming at creating an AutoExec macro that will run this script and export individual queries to different folder paths.

 

[Taruz]

Hi..

With this procedure, using the values ​​in the table, with one command, you can export queries..:

Dim rs As ADODB.Recordset


Set rs = New ADODB.Recordset
trz = "select * from tablename"
rs.Open trz, CurrentProject.AccessConnection

Do Until rs.EOF
DoCmd.OutputTo acOutputQuery, rs("query"), "ExcelWorkbook(*.xlsx)", _
 rs("Export Path") & rs("User Friendly Name") & ".xlsx", False, "", , acExportQualityPrint

rs.MoveNext

Loop

rs.Close
 
Set rs = Nothing

 

[a497253]

I had to enable the Microsoft Activex data objects 2.1 library but after that it worked like a charm.

Thanks!