Access: to export fields to excel based on a query

Question

I am looking to export a few files into Excel from MS Access based on querying a table.

It's like this the query will be

select * from table where field = 0

I would like to loop the query till field is 9 and I want to save each result in a different name like field1, field2

How will this have to be modified?

strqry = "select * from table where field = 1"
DoCmd.TransferSpreadsheet acExport, _
                            acSpreadsheetTypeExcel9, _
                            "strqry", _
                            "C:\Reports\Data_Analysis1.xls", _
                            True

Also how do I name the first sheet as field1 and in next loop field2

Answer 1

Speicify the sheet name in the "Range" field of the TransferSpreadsheet Command.

DoCmd.TransferSpreadsheet acExport, _
                            acSpreadsheetTypeExcel9, _
                            "tablename", _
                            "filename.xls", _
                            True, _
                            "sheetname"

Answer 2

So really what you want is something like this

dim i as integer

for i = 1 to 9 
    strqry = "select * from table where field = "  & i
    DoCmd.TransferSpreadsheet acExport, _
                              acSpreadsheetTypeExcel9, _
                              strqry, _
                              "filename.xls", _
                              True, _
                              "sheetname_" & i
next i

Answer 3

I finally got it done this way

For i = 1 To 9
    CurrentDb.QueryDefs("MyQuery").SQL = "Select * from table Where field = " & i
    DoCmd.TransferSpreadsheet acExport, _
                                acSpreadsheetTypeExcel9, _
                                "MyQuery", _
                                "C:\Reports\Data_Analysis.xls", _
                                True, _
                                "Field_" & i
Next i