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/answers/550169-export-each-record-into-separate-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