Export Access Table into multiple Excel Workbooks

[J]

I have an access table that has information sorted by group, i would like a
macro or VB code that would export data from one access table into different
Excel workbooks depending on their group. Example:

Region Group First Name last Name
A Delta John Doe
A Delta Jane Doe
A Gamma Frank Doe
A Gamma Steve Doe

I would like to export the above table so that it save is into a folder
called Region A and as an Excel Workbook as a particular group (Delta, Gamma,
etc).
thank you

 

[Ken Snell \(MVP\)]

Here's some VBA code that I wrote for an earlier poster that exports
filtered data from a single table to separate worksheets within a single
EXCEL file. You'll need to modify the code to use your specific table/query
names and field names, and then it should work for you. If you have
difficulty making the necessary changes, post back with more specific
information about your table/query and field names.


Generic 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
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 manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
' *** 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 EmployeesTable WHERE " & _
"ManagerID = " & 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 of code