I have a challenging Access to Excel export problem: I would like to export the results of a query to excel such that each record is exported to it's own excel workbook, and within each workbook, I would like certain data fields to appear on different worksheets. I was able to export to unique workbooks with the following code:
My problem is that all of the output from this goes onto one worksheet within each workbook. How could I get certain data fields to go into separate worksheets within each workbook?
Please help.
Code:
Option Compare Database
Option Explicit
'Declare the record selector criteria
Dim strCriteria As String
Function funGetCriteria()
'set up the alias for query to use
funGetCriteria = strCriteria
End Function
Sub ExportToXL_test()
'test sub for debugging
'Declare variables
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSql As String
Dim strPath As String
'Set path to where files will be output
strPath = "C:\"
'SQL to select distinct record
strSql = "SELECT DISTINCT tblSherriesExport.AgreementID FROM tblSherriesExport"
'Set-up Loop function to select each record in sequence
Set qdf = CurrentDb.CreateQueryDef("", strSql)
Set rs = qdf.OpenRecordset
Do While Not rs.EOF
strCriteria = rs!AgreementID
'Debug.Print funGetCriteria
'Export files to Excel
DoCmd.OutputTo acOutputQuery, "qrySherries", acFormatXLS, strPath & strCriteria & ".xls"
'Loop to next record
rs.MoveNext
Loop
Set rs = Nothing
Set qdf = Nothing
End SubMy problem is that all of the output from this goes onto one worksheet within each workbook. How could I get certain data fields to go into separate worksheets within each workbook?
Please help.
