none
[Access 2003 + VBA] How to export SQL SELECT Result in Excel file instead of Access ListBox? RRS feed

  • Question

  • Hi,

    I have VBA Form in MS Access Database, an execute query button run a SQL SELECT query and show result in an external ListBox.

    What I want is when I hit the button, it saves the result in an Excel file automatically!

    How  do I do that, please?

    Regards

    Wednesday, October 26, 2011 8:44 PM

Answers

  • Hi Iboumiza,

     

    You can do this by using the DoCmd.TransferSpreadSheet () Method in combination with a QueryDef, for example:

    Private Sub cmdSQLtoXLS_Click()
    
    Dim strSQL As String
    Dim strQry As String
    
    strSQL = "SELECT * FROM tblYourTableName"
    strQry = "TempQueryName"
     
    Set Db = CurrentDb
    Set Qdf = Db.CreateQueryDef(strQry, strSQL)
     
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
       strQry, "C:\YourPathName\YourFileName.xls", True
     
    DoCmd.DeleteObject acQuery, strQry
    
    End Sub
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by iboumiza Thursday, October 27, 2011 1:32 PM
    Wednesday, October 26, 2011 9:03 PM
    Moderator

All replies

  • Hi Iboumiza,

     

    You can do this by using the DoCmd.TransferSpreadSheet () Method in combination with a QueryDef, for example:

    Private Sub cmdSQLtoXLS_Click()
    
    Dim strSQL As String
    Dim strQry As String
    
    strSQL = "SELECT * FROM tblYourTableName"
    strQry = "TempQueryName"
     
    Set Db = CurrentDb
    Set Qdf = Db.CreateQueryDef(strQry, strSQL)
     
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
       strQry, "C:\YourPathName\YourFileName.xls", True
     
    DoCmd.DeleteObject acQuery, strQry
    
    End Sub
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by iboumiza Thursday, October 27, 2011 1:32 PM
    Wednesday, October 26, 2011 9:03 PM
    Moderator
  • Hi Daniel,

    I added these 2 lines to your code:

    Dim db As Database
    Dim Qdf As QueryDef
    

    and it's exactly as I want thank you man!

    Thursday, October 27, 2011 1:32 PM
  • How would i use this exact same code if i have a listbox that is filtered by a few combo boxes and i want only the filtered result to be exported

    Thanks in advance

    Monday, June 25, 2012 3:21 PM
  • thank you so much!
    Tuesday, July 16, 2019 6:24 AM