none
[VBA + SQL + Access 2k3] How to export multiple queries into one excel Workbook, but, multiple Worksheet? RRS feed

  • Question

  • Hi,

    this is the code I use to export report to an excel file:

    Public Sub btnGenExport()
    Dim strSQL As String
    Dim db As Database
    Dim Qdf As QueryDef
        strSQL = "SQL QUERY;"
        strQry = "REPORT_QUERY"
     
    Set db = CurrentDb
    Set Qdf = db.CreateQueryDef(strQry, strSQL)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, _
       strQry, "C:\Program Files\Export\GENERAL_EXPORT.xls", True
     
    DoCmd.DeleteObject acQuery, strQry
    Application.FollowHyperlink "C:\Program Files\Export\GENERAL_EXPORT.xls"
    End Sub

    What I want to do is, execute multiple queries at the same time (press one button) and have them populate in the same WorkBook but in different WorkSheet automatically!

    How can I do it, please?

    Regards

    Monday, May 7, 2012 5:56 PM

Answers

  • You can use whatever you like as sheet name, it doesn't have to be Sheet1, Sheet2, ... or Feuil1, Feuil2, ...

    Regards, Hans Vogelaar

    • Marked as answer by Admin-Dev Tuesday, May 8, 2012 4:33 PM
    Monday, May 7, 2012 9:01 PM

All replies

  • You can specify the name of the sheet in the Range argument:

    Public Sub btnGenExport()
        Dim strSQL As String
        Dim dbs As Database
        Dim qdf As QueryDef
        strQry = "REPORT_QUERY"
    
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef(strQry)
    
        strSQL = "SELECT ... FROM ThisTable"
        qdf.Sql = strSQL
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, _
            strQry, "C:\Program Files\Export\GENERAL_EXPORT.xls", True, _
            "Sheet1"
    
        strSQL = "SELECT ... FROM ThatTable"
        qdf.Sql = strSQL
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, _
            strQry, "C:\Program Files\Export\GENERAL_EXPORT.xls", True, _
            "Sheet2"
    
        ' Etc.
    
        DoCmd.DeleteObject acQuery, strQry
        Application.FollowHyperlink "C:\Program Files\Export\GENERAL_EXPORT.xls"
    End Sub


    Regards, Hans Vogelaar

    Monday, May 7, 2012 8:22 PM
  • Hi Hans,

    this code works perfectly, but, I'm in Canada so we have french and english Excel installed!

    For some it's Sheet1 and for the others it's Feuil1...

    Is there a way to have it create Worksheets automatically, please?

    Regards

    Monday, May 7, 2012 8:55 PM
  • You can use whatever you like as sheet name, it doesn't have to be Sheet1, Sheet2, ... or Feuil1, Feuil2, ...

    Regards, Hans Vogelaar

    • Marked as answer by Admin-Dev Tuesday, May 8, 2012 4:33 PM
    Monday, May 7, 2012 9:01 PM
  • Perfect,

    I'll adapt my report based on that

    Thanks

    Tuesday, May 8, 2012 4:34 PM