Want to dump SQL query results to Excel

[cbjones]

I know this is a question that comes up frequently, but I have yet to find anything that works. Here is my situation:

Facts:
I have written a moderately complex query which creates 28 columns of data
Each column has a name {header}
It works

Challenge:
Run the query in SQL Query Analyzer and, as a part of the query execution, dump the information to Excel in text format.

I can do this manually by copy and paste but I have to add the headings one by one so I can live without unraveling this one, however, that is certainly unsatisfactory.

Can anyone help me?

cbjones {learning slowly}
Frustrated Newbie

[ADezii]

@cbjones
You can use the TransferSpreadsheet() Method. The following Code will Export (Argument 1) the qryEmployees Query (Argument 3) to an Excel SpreadSheet (Argument 2) as Test.xls in the C:\Stuff\ Folder (Argument 4). The first Row of the SpreadSheet will consist of the Column Names (Argument 5 - True);

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryEmployees", "C:\Stuff\Test.xls", True

[cbjones]

Thank you! I negected to mention that I am using MS SQL 2000 Server...

Now I show my newbie side:
I know I have made an mistake of some kind. I get the following error:
Could not find stored procedure 'DoCmd.TransferSpreadsheet'.

Questions:
Where is this code placed in my query?
Do I need to create a "C:/Stuff" directory?
Do I need to set up an Excel file?

Thanks again!
Struggling

[ADezii]

@cbjones

Where is this code placed in my query?

No where, it's executed independently of your Query.

Do I need to create a "C:/Stuff" directory?

No, create any Absolute Path that you wish.

Do I need to set up an Excel file?

No.