I suggest you create a copy of your form that has the subform, to practice. You can create a Recordset Clone with the following... You will need to replace SubformControl with the name of your subform control.
Code:
if me.dirty then me.dirty = false
Dim rsClone as DAO.recordset
set rsClone = me.SubformControl.form.recordsetclone
if rsclone.eof then
msgbox "No records found."
set rsclone = nothing
exit sub
end if
Place the above code snippet in the Click event procedure of a Command Button Control. Your button should be part of the Main form and not part of the subform. As you add/type code to your main form's module, you will want to Compile your code. Use Debug>Compile Database from the Menu Bar within the VBA editor.
If everything compiles OK, youi will want to add some more code. You can double check that your rsClone object that you instantiated did, indeed, instantiate by typing rsClone and dot (rsClone.) to get intellisense to appear.
Once you have your recordset object good to go, you can add additional code to create an object to Automate Excel. The additional code will look something like this.
Code:
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Add
.Sheets("Sheet1").Select
.ActiveSheet.Range("A2").CopyFromRecordset rsClone
For i = 1 To rsClone.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = rsClone.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
End With
This is a very simple and straightforward way to export data to an Excel spreadsheet. If you want to do additional stuff, like save the file, add additional formatting, etc., you will need additional code. This example uses Late Binding. If you want to do a lot of Automation of your Excel object you may want to consider Early Binding. Early Binding requires a reference to a library but, it will provide Intellisense for your instantiated objects.
I tested the following in a new blank DB. The subform control's name was ctrForExport. The code is a complete Sub Procedure for a Click event for a Command Button named Command0
Code:
Private Sub Command0_Click()
If Me.Dirty Then Me.Dirty = False
Dim rsClone As DAO.Recordset
Set rsClone = Me.ctrForExport.Form.RecordsetClone
If rsClone.EOF Then
MsgBox "No records found."
Set rsClone = Nothing
Exit Sub
End If
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Add
.Sheets("Sheet1").Select
.ActiveSheet.Range("A2").CopyFromRecordset rsClone
For i = 1 To rsClone.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = rsClone.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
End With
End Sub