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

atfaniqbal

Registered User.
Local time
Today, 19:27
Joined
Jun 22, 2013
Messages
12
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
 
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.
 
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.
 
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.
 
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
 
This Is Error While Opening Your Attachment ... :(
 

Attachments

  • Error.Jpg
    Error.Jpg
    10.4 KB · Views: 110
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

Code:
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
 

Users who are viewing this thread

Back
Top Bottom