I've modified some code (from my library) to what I believe will work for
your table name and field name information. But, it's possible that some
tweaking might be needed to "debug" the code, so feel free to post back with
questions/problems.
I've written this as a function, so put this code in a new Module object (do
not name the module the same name as the function). You then can call this
subroutine from another VBA procedure or from a macro or from an actual
event of a control on a form. If you post back with more information about
the context in which you want to run this export process, I can provide
additional suggestions for how to set it up.
Customized code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
worksheets in a single EXCEL file
----------------------------------------------------------
'Start of code
Public Sub ExportCityQueries()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Const strFileName As String = "PutEXCELFileNameHereWithout.xls"
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of City values
strSQL = "SELECT DISTINCT City FROM tbl1;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of city values and create a query for each value
' so that the data can be exported
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM tbl1 WHERE " & _
"City = '" & rstMgr!ManagerID.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strFileName & ".xls"
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
End Sub
'End of code