export each record into separate excel file

[aran]

I am trying to export each record in an Access database into its own separate Excel file. Is there an easy way to do this? Please dumb it down for me as much as possible. Thank you.

Also is this possible using a macro?

[PEB]

Hi,

It's possible using Excel Automation in Vb...

See some of the last threaDS in thescripts.com in the Access forum ther is a lot information about Connecting to Excel

search about

CreateObject

Excel

Best regards

[MMcCarthy]

You would need a function built in vba in a module in the database. This is the simplest solution I can think of:

Create a second table with the fields from the table or query you need to output.

Then put the following code in any module. Please check this code for the instructions on the changes you need to make.

 
Function outputRecords()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim path As String
Dim i As Integer
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("TableOrQueryName") ' replace with your table or query name
    Set rs2 = db.OpenRecordset("NewTableName") ' replace with new table name
 
    ' Replace the path with whichever folder you want to put the spreadsheets in
    path = "C:\"
 
    rs1.MoveFirst
    Do Until rs1.EOF
        rs2.AddNew
        For i = 1 To 20 ' replace 20 with the number of fields in your table or query
            rs2.Fields(i) = rs1.Fields(i)
        Next i
        rs2.Update
        DoCmd.OutputTo acOutputTable, "NewTableName", FormatXLS, path & "RecordNo" & i & ".xls"
        DoCmd.RunSQL "DELETE * FROM NewTableName;"
        rs1.MoveNext
    Loop
 
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
 
End Function
 
 

open a macro and select the Run Code option. Then put in the function name as below.

outputRecords ()