Hi Hennie,
I think I have it...
Here is the code - an explanation is below...
Let me know if you get stuck on any part of it.
Code:
Private Sub CommandExport_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim path As String
Dim str1Sql As QueryDef
Dim strCrt As String
Dim strDt As String
Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
Set dbs = CurrentDb
path = "C:\DataExport\"
strSQL = "SELECT DISTINCT Site_ID FROM MyTable"
rs.Open strSQL, cn
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do While Not rs.EOF
strCrt = rs.Fields(0)
strSQL = "SELECT * FROM MyTable WHERE Site_ID=" & strCrt
Set qdf = dbs.CreateQueryDef("qryExport", strSQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExport", path & "SiteData-" & strCrt & ".xls", True
DoCmd.DeleteObject acQuery, "qryExport"
Set qdf = Nothing
rs.MoveNext
Loop
End If
End Sub
OK - Explanation...
What you want to do, is create a button on your form, and then add add a Click Event. Select 'Code Builder' and then past the code inside of the procedure. View the code below on the Wrox site to see the comments.
Private Sub CommandExport_Click()
This part sets up all the variables and objects that you need
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim path As String
Dim str1Sql As QueryDef
Dim strCrt As String
Dim strDt As String
Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
Set dbs = CurrentDb
Set your export path here
path = "C:\DataExport\"
This creates a set of all the Site_IDs in the table. Change MyTable to your table name
strSQL = "SELECT DISTINCT Site_ID FROM MyTable"
rs.Open strSQL, cn
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
For each unique id, the code below creates a query in Access called 'qryExport'. This query holds all the records for the specific ID. It then exports that query to Excel, and deletes the query, before moving onto the next Site_ID. You will need to change MyTable to your table Name, and make adjustments to the FileName if needed.
Do While Not rs.EOF
strCrt = rs.Fields(0)
strSQL = "SELECT * FROM MyTable WHERE Site_ID=" & strCrt
Set qdf = dbs.CreateQueryDef("qryExport", strSQL)
Right now it will export to 'SiteData-55.xls' for Site_ID = 55
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExport", path & "SiteData-" & strCrt & ".xls", True
DoCmd.DeleteObject acQuery, "qryExport"
Set qdf = Nothing
rs.MoveNext
Loop
End If
End Sub
All the best, and Welcome to the forum.
Mike
Mike
EchoVue.com
Spliting a data table
