EXPORT MULTIPLE QUERIES TO EXCEL

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

EXPORT MULTIPLE QUERIES TO EXCEL

Post by Michael Abrams »

I know this has been asked/answered before, but I cannot find it.

I am running a report based on 4 queries. I was exporting each to its own Excel spreadsheet.

Now I want to export all 4 into one spreadsheet and combine all 4 on one tab.

Obviously the code below doesn't work - I think it's close, but needs a tweak.

Code: Select all

DoCmd.OutputTo acQuery, "SP-FAIR HOSPICE MEMBERS", "Microsoft Excel Workbook(*.XLSX)", _
R:\QRS HMOS 2022\ABC INC 2022\ & strMonth & "\HOSPICE REPORTS\QRS HOSPICE REPORT.XLSX\SHEET1", False, ""

DoCmd.OutputTo acQuery, "SP-XYZ FAIR HOSPICE MEMBERS", "Microsoft Excel Workbook(*.XLSX)", _
R:\QRS HMOS 2022\ABC INC 2022\ & strMonth & "\HOSPICE REPORTS\QRS HOSPICE REPORT.XLSX\SHEET1", False, ""

DoCmd.OutputTo acQuery, "SP-XYZ-FAIR HOSPICE MEMBERS", "Microsoft Excel Workbook(*.XLSX)", _
R:\QRS HMOS 2022\ABC INC 2022\ & strMonth & "\HOSPICE REPORTS\QRS HOSPICE REPORT.XLSX\SHEET1", False, ""

DoCmd.OutputTo acQuery, "SP-XYZ-FAIR HOSPICE MEMBERS", "Microsoft Excel Workbook(*.XLSX)", _
R:\QRS HMOS 2022\ABC INC 2022\ & strMonth & "\HOSPICE REPORTS\QRS HOSPICE REPORT.XLSX\SHEET1", False, ""

xps35
NewLounger
Posts: 14
Joined: 27 Sep 2022, 07:22
Location: Schiedam, NL

Re: EXPORT MULTIPLE QUERIES TO EXCEL

Post by xps35 »

Make a UNION query with the 4 queries and export that one to Excel.
Groeten,

Peter

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: EXPORT MULTIPLE QUERIES TO EXCEL

Post by Michael Abrams »

Thank you - I will try that.

May be first time in 20 years I tried a Union query :scratch:

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: EXPORT MULTIPLE QUERIES TO EXCEL

Post by HansV »

Do the four queries have the same columns/fields, in the same order? If so, the SQL for the union query could look like this:

SELECT * FROM [SP-FAIR HOSPICE MEMBERS]
UNION SELECT * FROM [SP-XYZ FAIR HOSPICE MEMBERS]
UNION SELECT * FROM [SP-XYZ FAIR HOSPICE MEMBERS]
UNION SELECT * FROM [SP-XYZ FAIR HOSPICE MEMBERS]

Change the names of the queries (currently, the 2nd, 3rd and 4th are the same), then save the union query.
You'll need only one DoCmd.OutputTo statement. (I'd prefer DoCmd.TransferSpreadsheet though)

P.S. Thanks for your reply, Peter (xps35), and welcome to Eileen's Lounge!
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: EXPORT MULTIPLE QUERIES TO EXCEL

Post by Michael Abrams »

They do have the same fields.
Will try it shortly.

Thank you both SO much !

Michael

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: EXPORT MULTIPLE QUERIES TO EXCEL

Post by Michael Abrams »

Works perfectly !

Thank you and have a great week end!

Michael