Output a Filtered Query to Excel

[ted.martin]

Not exactly ground breaking but this code I put together opened up other opportunities to use it as the Docmd.Outputto code requires a query and for a variety of reasons, I did not want to create one.

The Form FP-Dates is nothing more than two unbound controls that are the date range. Remember in SQL UK dates must be converted to USDates mm/dd/yy

Public Function MakeUSDate(myUSdate As Variant)

If Not IsDate(myUSdate) Then Exit Function
MakeUSDate = "#" & Month(myUSdate) & "/" & Day(myUSdate) & "/" & Year(myUSdate) & "#"

End Function

Here is the main body of the code.

    Dim Db As Database
    Dim tmpqdfOP As QueryDef
    
    Set Db = CurrentDb
    
    Set tmpqdfOP = Db.CreateQueryDef("Qtmp-OP_Timesheets", "SELECT Timesheets.* FROM Timesheets " & _
    "WHERE (((Timesheets.WorkDate) >= " & MakeUSDate([Forms]![FPM-Dates].[txtStartDate]) & " And " & _
    "(Timesheets.WorkDate) <= " & MakeUSDate([Forms]![FPM-Dates].[txtEndDate]) & "))" & _
    "ORDER BY Timesheets.ProjectNo, Timesheets.WorkDate, TimeSheets.LogOnName;")
    
    DoCmd.OutputTo acOutputQuery, "Qtmp-OP_Timesheets", "ExcelWorkbook(*.xlsx)", _
    "c:\temp\AllTimesheets_" & Format(Forms![FPM-Dates].[txtStartDate], "ddmmyy") & "_to_" & Format(Forms![FPM-Dates].[txtEndDate], "ddmmyy") & ".xlsx", _
    False, "", 0, acExportQualityPrint
    
    'Clean Up
    Db.QueryDefs.Delete tmpqdfOP.Name   ' Must delete as would error on re-Run
    Set tmpqdfOP = Nothing
    Set Db = Nothing
    
    MsgBox "Your Timesheets for your chosen period have been exported to Excel", vbInformation, "Path  " & "c:\temp\AllTimesheets_" & _
    Format(Forms![FPM-Dates].[txtStartDate], "ddmmyy") & "_to_" & Format(Forms![FPM-Dates].[txtEndDate], "ddmmyy") & ".xlsx"

    DoCmd.Close acForm, "FPM-Dates"

 

[boblarson]

That would be one way.

Another is to use my code found here (reusable) and to pass it a SQL Statement. No query defs needed.