Creating multiple excel files per change in table field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to create a macro that will create a new excecl output file for
each change in a field. That is, I have a table with multiple values in a
field. I would like a new file to be created for each different value in a
field. Each of these files will contain all the records with like values in
that field. Because the values can be different each time the database is
used, I dont have the ability to specify what the field criteria is. I am
trying to prevent the need to create a make table qurey for each value
possibility. Please contact me if this is not clear.
 
Hi Callen,

Can't be done with a macro: you have to use VBA code. This demo code
shows one simple way. It needs a query called qryXLTemp (the original
contents of which get overwritten). The table in my test database is
[AddrCent], and I've separated the records according to the value of the
[City] field.

Sub ExportPerValueInColumn()

Dim dbD As DAO.Database
Dim rsValues As DAO.Recordset
Const BaseName = "C:\Temp\Jay\ExportFile_"


Set dbD = CurrentDb()
'Get list of distinct values in field
Set rsValues = dbD.OpenRecordset( _
"SELECT DISTINCT City FROM AddrCent ORDER BY City;")

Do Until rsValues.EOF
'Build SQL SELECT statement to return subset of records
dbD.QueryDefs("qryXLTemp").SQL = "SELECT * FROM AddrCent " _
& vbCrLf _
& "WHERE City = '" & rsValues.Fields("City").Value & "'" _
& vbCrLf _
& "ORDER BY LastName, FirstName;"

Debug.Print "Exporting records for " _
& rsValues.Fields("City").Value

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryXLTemp", _
BaseName & rsValues.Fields("City").Value & ".xls", True
rsValues.MoveNext
Loop

rsValues.Close
End Sub
 
Back
Top