Export to Multiple workbooks

[Mike Weeks]

I am trying to export information from a query to multiple work books.
However I would like to have it create a new workbook at every name change.
AN I do not want to make 700 queries, 1 for each name.

Any suggestion of how to export a each name change?

 

[Dale Fye]

Mike,

You could create a loop in your code to loop through the "Names". But
first, I would copy the query that you are currently using and save it as
something like "qry_ExportCopy". Then, write a some code in the click event
of a control on your form, something like:

Private cmd_Export_Click

dim strSQL as string
Dim rs as dao.recordset

strSQL = "SELECT DISTINCT [NameField] FROM yourOriginalQuery " _
& "ORDER BY [NameField]"

SET rs = currentdb.openrecordset strsql

WHILE NOT RS.EOF
strSQL = "SELECT * FROM yourOriginalQuery " _
&"WHERE [NameField] = '" & rs("NameField") & "'"
currentdb.querydefs("qry_ExportCopy").SQL = strsql
docmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, _
"qry_ExportCopy", _
"C:\Temp\" &
rs("NameField") & ".xls"
rs.movenext
WEND
rs.close
set rs = nothing

End Sub

Hope this gives you some good ideas.

Merry Christmas
Dale