Export Query into Excel

[system243trd]

I am trying to create a button that exports data into a brand new Excel spreadsheet on my c:\ drive.



Can anyone help?

[alansidman]

Assume your query is called MonthlyActivity. Written for Access 2003 and Excel 2003. Try this:

Private Sub ExportQuery_Click()
On Error GoTo Err_ExportQuery_Click
Dim reportName As String
Dim theFilePath As String


    reportName = "MonthlyActivity"


       
theFilePath = "C:\Drive"
theFilePath = theFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
      
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True


MsgBox "Report has been run."


   


Exit_ExportQuery_Click:
    Exit Sub


Err_ExportQuery_Click:
    MsgBox Err.Description
    Resume Exit_ExportQuery_Click
    
End Sub