Access Query - Export to multiple Excel files

[WhosUsingMyName]

I have a basic access query (MACs Report Template) that I need to export into multiple excel files based on the 1st field (Plan ID).

Example:

PLAN ID	Number	Amount	Status
AM141	12345	100	Disconnected
AM141	54321	5000	Active
AM142	11122	2000	Disconnected
AM155	22334	500	Disconnected

I need this to create a spreadsheet for each unique PLAN ID. Ideally i want it to export the following:

MACs Report AM141 20150521.xls (both records above should be in this report)


MACs Report AM142 20150521.xls
MACs Report AM155 20150521.xls

[June7]

Looping structure code in VBA can accomplish. Review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

[WhosUsingMyName]

I think the second section of what you sent is what I need to do, but i am a complete beginner and do not know how to modify this code to work in my database. Can you explain in more detail?

Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)

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 EXCEL files. In this sample code, the employees assigned to each manager are exported to separate EXCEL files, one file for each manager.

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 strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
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 and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each ManagerID
' 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, and
' *** 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 and 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.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

[June7]

Follow the green text instructions.

Instead of ManagerID you want PlanID and instead of EmployeesTable you want your table or query (MACs Report Template).

Change variable names as appropriate to be more meaningful in your db, such as strMgr could be strPlan.

Adapt or remove other code as appropriate. You won't need the DLookup for manager name.