Need Code For This Query To Export The Result To Excel File

[atfaniqbal]

FileSent Is Table / Database Name

SELECT FileSent.[Patient#], FileSent.PatientName, FileSent.EpisodeKey, FileSent.DoctorName, FileSent.Mark, FileSent.FinancialType
FROM FileSent
WHERE (((FileSent.Mark)="1"));

DoCmd.TransferSpreadsheet acExport, 5, tablename:="FileSent", FileName:="FileSent_Excel.xls"
Kill ("FileSent_Excel.xls")
DoCmd.TransferSpreadsheet acExport, 5, tablename:="FileSent", FileName:="FileSent_Excel.xls"

Please Help...
Thanks

 

[JHB]

... Please Help...
Thanks

What is the problem, (some error message)?

 

[atfaniqbal]

I need the complete code to compile the above query & exporting the result using the below DoCmd.TransferSpreadSheet Code From a vba Export_Click() button.

Please...
Thanks.

 

[Alansidman]

Look at the attached example

 

[atfaniqbal]

Dear The Database Attached Gives An Error Saying Unrecognized Database Format.
I Use Access 2007. Is This Attached Database Supported With This Version Of Access.

 

[Pat Hartman]

You have created a query to select the data you want to export but your TransferSpreadsheet Method refers to the table. It should reference the querydef you created instead.

 

[atfaniqbal]

So What Will Be The Code Using That Reference..

 

[Alansidman]

Did you look at the VBA in the example I posted? There is a complete example of the code for exporting a query to Excel. It is in Access 2007-2010 format. It opens ok for me.

Alan

 

[atfaniqbal]

This Is Error While Opening Your Attachment ...

 

[Alansidman]

Still don't know what the issue is with opening the example. Below is code for two queries in an option frame. Depending on which one is selected by the user will determine which one is transferred to Excel

Private Sub Command3_Click()
 
    Dim reportName As String
    Dim theFilePath As String
 
''    On Error Resume Next
    Select Case Me.Frame4.Value
        Case 1
            reportName = "Query1"
        Case 2
            reportName = "Query2"
    End Select
 
    theFilePath = "C:\Users\" & Environ("UserName") & "\Desktop\"
    theFilePath = theFilePath & reportName & ".xlsx"
 
    DoCmd.TransferSpreadsheet acExport, 10, reportName, theFilePath, True
    MsgBox "Check your Desktop."
 
End Sub