export report to multiple excel files?

[Guest]

I have a query that returns a very large number of records. I am grouping by
one of the variables and was wondering if there was any possible way at all
for me to Export the data to multiple excel files based off of the grouping.

 

[Guest]

I would suggest you modify your query so that it returns a recordset for one
group at a time and export it. You can put this in a control loop so you can
do them all at one time:
Create a query that returns a unique set of values for the field you are
grouping on. Use this as the source for your control loop and to pass a value
to the query that returns the data for the export:

 

[John Nurick]

Here's one way of doing it. You'll need:

1) a form (let's call it frmXXX) with an unbound textbox called
txtGroupName and a commandbutton called cmdExportByGroup

2) a query that gets the fields you need from the table, in the order
you want. Don't bother with grouping; instead make it a parameter query
that gets the parameter value from txtGroupName and returns only the
records that match it. The SQL view of the query might look like this;

SELECT * FROM MyTable
WHERE MyField=Forms![frmXXX]![txtGroupName];

Then use code like this in the button's Click event procedure to do the
work:


Private Sub cmdExportByGroup_Click()
Dim dbD As DAO.Database
Dim rsGroupList As DAO.Recordset
Dim strExportPath As String
Dim strFileSpec As String

Set dbD = CurrentDb()

'location for exported files
strExportPath = "C:\Folder\Subfolder\"

'Get a recordset containing the values to group by
Set rsGroupList = dbD.OpenRecordset( _
"SELECT DISTINCT GroupName FROM MyTable;", dbOpenForwardOnly)

With rsGroupList
Do Until .EOF 'iterate through list
'put group name in textbox on form so query can find it
Me!txtGroupName.Value = .Fields(0).Value
'build the file name
strFileSpec = strExportPath & .Fields(0).Value & ".xls"
'export the query
DoCmd.TransferSpreadsheet TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel8, _
TableName:="qryExportByGroup", _
filename:=strFileSpec
Loop
End With

'tidy up
rsGroupList.Close
Set dbD = Nothing

End Sub