CODEWITHSUNDEEP
ms-access
tksy
tksy

Reputation: 3529

Access: to export fields to excel based on a query

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

Upvotes: 1

Views: 1947

Answers (3)

BIBD
BIBD

Reputation: 15384

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

Upvotes: 2

LeppyR64
LeppyR64

Reputation: 5349

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

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

Upvotes: 1

tksy
tksy

Reputation: 3529

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

Upvotes: 2

Related Questions