How do I export all records from a form to an Excel worksheet

Question

edit: nevermind, I found a way to do it. I will post my answer as a separate post.

I have a continuous form containing multiple records. I would like to be able to export all record information on the form into an Excel worksheet named "RawData", where the Excel file is at some static location.

I tried Docmd.OutputTo, but that replaces all content with a single sheet containing the form records, and Docmd.TransferSpreadsheet seems to only import the text "F1" in cell A1 (dunno why). Maybe I am using them wrong?

Mike Mike · Accepted Answer · 2015-08-13T00:13:06

You basically have to open Excel and fill in each cell one by one. Or that's the only way I know how to do it at least... The original code is from here, but I've modified it since the original was only exporting one record.

Private Sub btnExportExcel_Click()
Dim appExcel As Object
Dim wkbWorkBook As Object
Dim wksSheet As Object

' creates Excel application
Set appExcel = CreateObject("Excel.Application")
' opens workbook for output
Set wkbWorkBook = appExcel.Workbooks.Open("C:\Users\mikec\Desktop\Book1.xlsx")
' get reference to sheet for output
Set wksSheet = wkbWorkBook.Worksheets("RawData")


' for each record, output all fields in the record
Dim rs As Recordset
Dim row As Integer: row = 1
Dim col As Integer: col = 1
Set rs = Me.Form.Recordset
If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do While Not rs.EOF
        Dim fld As Field
        For Each fld In Me.Recordset.Fields
            wksSheet.Cells(row, col) = fld
            col = col + 1
        Next fld
        rs.MoveNext
        col = 1
        row = row + 1
    Loop
End If

' save and quit
wkbWorkBook.Save
appExcel.Quit

Set wksSheet = Nothing
Set wkbWorkBook = Nothing
Set appExcel = Nothing
End Sub

How do I export all records from a form to an Excel worksheet

1 Answers