How to export an unsaved query to a new excel workbook?

[normalice]

I would like to generate the SQL text for data to export to an excel workbook. Further, I would like the workbook to have no saved location - just a new workbook (the default "book1", "book2"...). Lastly, the format of the data (pivot table, datasheet..) should also be optional.

I know access must be capable of this, because pushing the "Export to excel" button does exactly what I'm looking for, with any active query or table. Is there any way to harness the automation of the "Export to Excel" button through VB?

Everywhere I look seems only to have ways to export an existing query to an existing workbook.

[NeoPa]

This would not be remotely trivial.

I could only envisage doing this through Excel Automation (See Application Automation) using a process whereby you run the unsaved query; copy the results to the Clipboard; Open Excel and a new workbook within it; Paste those same results into the ne workbook; Leave the application open and visible. Not for the feint-hearted though.

[normalice]

that's actually what i'm using now, but it's a lot slower than just pushing the "Export to excel" button you see when you have a query, and I can't make the excel display the data as a pivot table, because I'm not all that familiar with excel vb. I would just tell people to use access's own "Export to Excel" button, but I don't want to display the query, and I'd rather have the export button on a form anyway.

is there a way to open a query but not show it, and then call the export to excel command, something from the "DoCmd.DoMenuItem" thing?

[NeoPa]

If there were (and frankly it's something I never do so I'm really not 100% sure) then I would expect to find it as a flavour of DoCmd.RunCommand(Command). I looked in the list for you under Excel; Analyse; Export; but all I could find were the following Commands. Unfortunately there is no specific help for each Command so you may want to experiment :

1. acCmdOutputToExcel
2. acCmdPivotTableExportToExcel
3. acCmdExport

Not much I know, but I have the feeling that was bolted on at some point with a view to providing an facility for an operator specifically, rather than for use in code. Who knows though? Maybe worth looking at these at least.