Ok, I guess the secret to this user group is that if you look around
the web long enough you'll eventually find the answer to any Access
question you have. :-)
Here's the code that will export several tables from a single Access
table, changing the name of each Excel table as it exports.
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
