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)", "firstname.lastname@example.org", "", "", "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.
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.