Results 1 to 2 of 2
  1. #1
    Join Date
    May 2013
    Posts
    6

    Unanswered: DoCmd To Output multiple query outputs to two separate spreadsheet tabs

    Hello, I regularly use DoCmd's to output query results into spreadsheets, and either email those out or store them in a network drive somewhere. I'm running into some issues trying to output two seperate queries onto two spreadsheet tabs, within the same workbook in Excel. I've been trial and erroring with DoCmd.

    I typically work with something like

    DoCmd.SendObject acQuery, "QUERYNAME", "ExcelWorkbook(*.xlsx)", "EMAIL ADDRESS HERE", "", "", " SubjectLine ", " Body of Email ", False, ""



    Trying to move towards something like:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_one", "Sell In File", , False
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_two", "Sell In File", , False
    DoCmd.SendObject acSendModule, "Sell In File", "ExcelWorkbook(*.xlsx)", "johnsmith@john.com", "", "", "Sell In Summary and Detail for Yesterday", False, ""


    The "acSendModule" piece I know is incorrect, but I've tried all of them.

    I'm stuck. The end result is to take both query outputs, put them on a spreadsheet on different tabs, and fire off an email. Any help would be greatly appreciated.

  2. #2
    Join Date
    Jun 2004
    Location
    NYC Area
    Posts
    24
    You cannot specify a sheet (tab) using the TransferSpreadsheet command. My suggestion is to...
    - Open an instance of Excel and create a new file
    - Rename the Sheet1 tab to whatever you want for the first query
    - Copy the renamed Sheet1 to whatever you want for the second query
    - Open a recordset with the results of the first query
    - Select Sheet1 and use the ActiveSheet.Range..CopyFromRecordset command
    - Repeat for 2nd query & save the file

    Will give you control of sheet names and you can build in ability to drop in comments like "... no data" if recordset has no records instead of having a blank worksheet.

    hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •