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]

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

You can use the TransferSpreads heet() 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.Transfer Spreadsheet'.

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]

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.Transfer Spreadsheet'.

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

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.