Trouble with TransferSpreadsheet Method 1

[socomfort]

I am very new at this so please bear with me. I am trying to send the result of a SQL select statement to a spreadsheet in MS Excel. The version of MS Office I am using is 2003.

After reading several FAQs, it looks like I cannot use DAO to export a query result directly from MS Access 2003 to MS Excel 2003.

So what I am doing first is to run a SQL append statement and put the data into a TEMP table (make table query). Then from there, I am using the following statement:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblTEMP", "C:\TempData.xls", True, "Download!", "A1:Q2"

The append query works and copies the data into the temp table. However, the above code is not working correctly.
I do not have much experience with this so it is difficult to gauge whether this statement is even correct. The help section in the IDE isn't much help either.

Anyone have any experience with this?

thanks,

Ben

 

[Remou]

Try:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblTEMP", "C:\TempData.xls", True, "Download!A1:Q2"

It is also possible to link a table or query to an Excel spreadsheet (Data->Get External Data).

 

[socomfort]

I am blind! Thankyou Remou for your kind help; that did the trick.

-Ben