It looks like this code should do the trick:
Code:
Dim rst As DAO.Recordset
Dim customQuery As String
Dim cnt As Integer
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim fileName As String
Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Worksheets(1)
Set rng = wks.Range("A2:I4001")
wks.Cells(1, 1).Value = "Generating data..."
Set rst = CurrentDb.OpenRecordset(customQuery)
If (rst.RecordCount > 0) Then
cnt = 1
For Each fld In rst.Fields
wks.Cells(1, cnt).Value = fld.Name
cnt = cnt + 1
Next fld
Call rng.CopyFromRecordset(rst, 4000, 26)
End If
fileName = ExportDir & "\Molenproductie" & DateTimeFrom & "-" & DateTimeTo & ".xls"
wks.SaveAs (fileName)
rst.Close
Set rst = Nothing
However, how would I keep it from overwriting the data each time I export? Like I want it to append the data to it. What I am trying to do is create a monthly report, but I need to export the data daily. So every day (Mon - Fri) I need to export the data, but have it appended (Tues, appended to Mon and so on).