Sorry... erroneously wrote code to create separate EXCEL files for each
location. Here is corrected code to use just one file with separate tabs:
'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstLoc As DAO.Recordset
Dim strSQL As String, strTemp As String
' assuming that LocationID is a text field
Dim strLocID As String
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
' Get list of location IDs -- note: replace generic table and field
names
' with the real names of the table and the ID field
strSQL = "SELECT DISTINCT LocationID FROM LocationsTable;"
Set rstLoc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of location IDs and create a query for each ID
' so that the data can be exported
If rstLoc.EOF = False And rstLoc.BOF = False Then
rstLoc.MoveFirst
Do While rstLoc.EOF = False
' Again, code assumes that LocationID is a text field
strSQL = "SELECT * FROM TableName WHERE " & _
"LocationID = '" & rstMgr!ManagerID.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strLocID
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace "C:\FolderName\FileName.xls" with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\FileName.xls"
rstLoc.MoveNext
Loop
End If
rstLoc.Close
Set rstLoc = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code
Let me know of errors / questions.
--
Ken Snell
<MS ACCESS MVP>
Ken Snell (MVP) said:
The approach would not require you to "know" when a loation has changed
in
the data. Instead, one would use a query that gives you the unique
locations, and then you filter the data for each manager name before
you
export the data.
Ken Snell
<MS ACCESS MVP>
I need to export data from Access 2002 into Excell 2002 - not normally
a
problem, but the boss wants a separate tab in excel for each location.
Can
Access be instructed to break a query results table or report at each
change
of location, then save that "page" to a separate tab in Excel?