How to split by the value of a field and copy to respectively excel files?

[Twilight]

There is a table tbl1,with several fields, one of them is City, with the value varies from city1 to city30.
I want to use Access Marco to automatically using function transferspreadsheet to copy this tbl1 into 30 excel sheets, by the value of city. For eg, select * from tbl1 where city value=city1 , then put the selected records into excel file “\…\…\city1.xls”,
Thus generates 30 excel files.
Is there a simple way to achieve this, avoid writing 30 transferspreadsheets in a macro?
Thanks.
Twilight

[JM420A]

I'm no pro, by any means, but I will throw this out there:

Look in to building code using Select Case or IF statements

Build a statement that looks for the city name and assign it to a variable

strCityName = rsCity.cityname

build your statement based off of that

Then you can have one long piece of code that checks the value of the city name. It may take longer to write, but it will work better.

I had a macro that ran like 10 open report functions, but it was printing blank forms, so I had to build code to check for null values, and if it was null, then it wouldn't print.

I'm not much help on the programmatic side, but I hope I gave you an idea of where else to start.

jm420a

[Twilight]

As I am green hand, i cann't understand your meaning, can you give a much detailed explanation? for eg, which moudle are you using?
very very thanks.

[NeoPa]

With a For loop in your code you can process through doing a TransferSpreadsheet of a query designed to SELECT only those records which match the current value (1 - 30).

With so little info provided I can be little more help I'm afraid.

[ADezii]

It ain't pretty, but it do work! (LOL).

Dim qdf As DAO.QueryDef
Dim intCounter As Integer
Dim qdfDelete As DAO.QueryDef
 
'Replace with your own Path to the *.xls Files
Const strPathToXLS As String = "C:\Test\"
 
DoCmd.Hourglass True
 
'Delete all 30 Querys temporarily created, should they already exist
For Each qdfDelete In CurrentDb.QueryDefs
  If Left$(qdfDelete.Name, 13) = "qryExportCity" Then
    CurrentDb.QueryDefs.Delete qdfDelete.Name
  End If
Next
 
For intCounter = 1 To 30
  Set qdf = CurrentDb.CreateQueryDef("qryExportCity" & CStr(intCounter), "Select * From tbl1 Where " & _
                                     "[City] = 'City" & CStr(intCounter) & "';")
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportCity" & CStr(intCounter), strPathToXLS & _
                                     "City" & CStr(intCounter) & ".xls", False
Next
 
DoCmd.Hourglass False

P.S. - After running this code, you are now left with 30 Querys named qryExportCity1 thru qryExportCity30 consecutively as well as 30 Spreadsheets named City1.xls thru City30.xls in the strPathToXLS Path. To Delete the 'Querys', simply run code lines 11 through 15 again.