22 access queries into one xlsx as different sheet tabs please

[fordowns]

Hello.

I have been shadowing your site for a while now. You guys seem to really have it together as a team across the world.
I have a stumbling block. I have 22 access queries that for some time now I have been trying to export results into one workbook, each having it's own sheet. May I ask permission to post what I have so far for some debugging help please?

I have table made that lists the 22 queries for looping to next but can not seem to learn how to incorporate this either.

I just started using access 2010 this year so yes I am another newbie. I understand if you guys are busy.
tx
Fordowns

[pbaldy]

You can certainly post what you have so far. A recordset would likely be used to loop the table.

[fordowns]

Here is what I have so far. It just throws them out into 22 spreadsheets. Then I have to manually combine them into one file.
I can not figure out looping it. I have all the query name and desired sheet names listed in a table called 'outputprocessing' . That is where I get stuck.
How to keep the export looping back to it instead of the long hand you see below. If anything obvious comes to mind, I would really appreciate it. I know most of you probably have a full plate already but thought maybe one of you has been down this road before maybe. Thanks so much.



Function ExportBackups()
On Error GoTo ExportBackups_Err

MsgBox "File should be at: \Planning Systems\Dimensions Snapshots", vbInformation, "File Loc of Final Export"
DoCmd.OutputTo acOutputQuery, "BA_01DEPT", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BA_01DEPT_20xx-MM-DDTxxxx.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BA_02ACCT", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BA_ACCT.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BA_03JOBCODE", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BA_JOBCODE.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BA_04BILLINGAREA", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BA_BILLINGAREA.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BA_05PHYSID", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BA_PHYSID.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BA_06PAYTYPE", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BA_PAYTYPE.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BAP1_01DEPT", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\20XX-MM-DDTxxxx_BAP1_DEPT.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BAP1_02ACCT", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BAP1_ACCT.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BAP1_03JOBCODE", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BAP1_JOBCODE.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BAP1_04PAYTYPE", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BAP1_PAYTYPE.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BAP1_05GLPERIOD", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BAP1_GLPERIOD.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BAP1_06PROVIDER", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BAP1_PROVIDER.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BAP1_07CPT", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BAP1_CPT.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BAP1_08DTYPE", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BAP1_DTYPE.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "BAP1_09FINCLASS", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\BAP1_FINCLASS.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "CAA_01PROJECT", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\CAA_PROJECT.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "CAA_02CODE", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\CAA_CODE.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "CAA_03PAYOR", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\CAA_PAYOR.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "HA_01MODEL", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\HA_MODEL.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "HA_02CODE", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\HA_CODE.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "HA_03PAYOR", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\HA_PAYOR.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "HA_04SCENARIO", "ExcelWorkbook(*.xlsx)", "\\work.edu\dfs\0004\0044\44001\Planning Systems\Snapshots\HA_SCENARIO.xlsx", False, "", , acExportQualityPrint


ExportBackups_Exit:
Exit Function

ExportBackups_Err:
MsgBox Error$
Resume ExportBackups_Exit

End Function

[pbaldy]

I'm just on an iPad right now, but have you tried outputting them all to the same file? Either OutputTo or TransferSpreadsheet will create different sheets for each.

[fordowns]

You have me curious now. Did not know this existed. Is this office 2013 command? Will it work in access 2010?
I put one of these statement before each query statement or after each ?? (quick sample where to insert it maybe?) Thank you again btw.

[June7]

See if Ken Snell example can help http://www.accessmvp.com/KDSnell/EXC...ExportSameFile

[fordowns]

I thank you. I did look at this while back. Can I replace his tables with queries and it still work same?

[June7]

Yes. Export table or query.

You can open a recordset of the table with query names, loop through that recordset, and use one dynamically structured TransferSpreadsheet line to export each query.