VBA to export each table row as it's own excel file

[Tom123456]

Hello,

I have a table that contains a number of rows - for each row i want to output an excel file for it

I've found a link that explains how to this https://bytes.com/topic/access/answe...ate-excel-file and amended the VBA for my tables and to adapt to the size of recordset.

However while it runs (no error messages) it does not create the files as expected (only 1 file with no relevant date) - uploaded is some sample data - Does anyone know what needs amending in this vba to fix?

Option Compare Database

 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
Dim lRecCount As Long

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("a") ' replace with your table or query name
    Set rs2 = db.OpenRecordset("b") ' replace with new table name
    lRecCount = DCount("[order no]", "a")
    ' Replace the path with whichever folder you want to put the spreadsheets in
    path = "\\nas01\kdrive\..Subscriptions\Data\Workbooks (CRM)\Delegates\output"
 
    rs1.MoveFirst
    Do Until rs1.EOF
        rs2.AddNew
        For i = 1 To lRecCount
            rs2.Fields(i) = rs1.Fields(i)
        Next i
        rs2.Update
        DoCmd.OutputTo acOutputTable, "b", acFormatXLS, path & "RecordNo" & i & ".xls"
        DoCmd.RunSQL "DELETE * FROM b;"
        rs1.MoveNext
    Loop
 
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
 
End Function

[ranman256]

instead of DoCmd.OutputTo
how about:
docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,"b",vFile,true

[OBP]

I have the code working to do what you want.
My code is on a form button on form1.
You will need to change the Folder location as it is using my test location.

ps I may have contaminated some of your other code and you do not need the msgboxes.

[Tom123456]

Hi Both,

Thanks for looking at my issue and your feedback

@ranamn - I've changed it too this and it runs without issue but no files are outputted?

@OBP - I've ran this and it creates the 3 files but with only the field headers (no rows in each file) - do you know what might have caused this?

Cheers,
Tom

[OBP]

Tom here are the 3 Excel sheets that it produced when I ran it.

[Tom123456]

Thanks - I was being silly and running my VBA attempt in the db rather then yours - I've converted it to a module & tested it and it works thanks for your help!

Tom