Export to specific Excel worksheet

[kcoyne]

I am trying to export a couple queries to different worksheets in the same excel document. When I looked in Help, it said that you cannot specify a workbook when exporting to 2000. I know there has to be code to do this but I haven't had any luck yet. The data needs to be in Excel for a report, so any suggestions on how to get it there would be greatly appreciated.
Thanks in advance!

 

[NewfieGolfer]

You can use the TransferSpreadsheet method to export to the specific excel file. In the range for the export, you can specify a worksheet by using the name of the worksheet and an exclamation mark.

example.

DoCmd.TransferSpreadsheet acExport, 8, _
"Table", "c:\example\ExcelTable.xls",True, _
"Worksheetname!A1:G12"

Hope this helps,
NG

 

[kcoyne]

Thanks for the input...I'm much closer. However, I am still getting an error message that the worksheet already exists. Does this mean that you cannot export to a worksheet that already exists? Please help me, I will try anything

Thanks again!

 

[NewfieGolfer]

Sorry about taking so long to respond, i'm working on an important project right now.

I checked out the code above it works with exporting tables to spreadsheets. I also tried it with queries and got the same results as you did. I'm not sure why this happens. A quick fix would be to append the queries to a table and export the table to the excel file.

NG