Hi Isaac,Please be more specific, thorough and detailed.
Thank you! sounds like a good place to start.You can use Excel automation to start up Excel, and use excel's Range.CopyFromRecordset method to place the results of a query (which you'd already have open as an Access DAO recordset, also in code).
Look into things like createobject("excel.application"), workbooks.open method, declaring and setting an excel worksheet, declaring and setting an excel range, range.copyfromrecordset, declaring and setting a dao.recordset.
is to do what to them? maybe you missed a wordThe goal is to multiple sheets to the workbook. is this how i should go about it? Please see the below
Dim strSaveFile as String
strSaveFile = "c:\MyDocs\MyExcelFile.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName1", strSaveFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName2", strSaveFile
etc. etc.
I agree. You can also preassign the sheet name, so renaming later won't be necessary.The simplest route would be to use DoCmd.TransferSpreadsheet from Access
Code:Dim strSaveFile as String strSaveFile = "c:\MyDocs\MyExcelFile.xlsx" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName1", strSaveFile DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName2", strSaveFile etc. etc.
This would store each query in a sheet with the query name as the sheet name, it would then be simple to change the sheet names if required
I meant send or export multiple queries to the workbook.is to do what to them? maybe you missed a word
I meant send or export multiple queries to the workbook.
Thanks
Did you not see the simplest method in post #7 ?I meant send or export multiple queries to the workbook.
Thanks
I just saw it. I will give it a go. I will let you know how it turns out!Did you not see the simplest method in post #7 ?
Thank you Isaac, I will give it a shot.I guess I'd have to agree with others - if you want the quick 'n dirty way, use docmd.transferspreadsheet.
I've recently had problems with that (such as cutting off text at 255 char's), which made me tend to eschew it altogether in favor of Copyfromrecordset. Which takes a bit more work, but also has fewer issues IMHO.
Hi Minty,The simplest route would be to use DoCmd.TransferSpreadsheet from Access
Code:Dim strSaveFile as String strSaveFile = "c:\MyDocs\MyExcelFile.xlsx" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName1", strSaveFile DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName2", strSaveFile etc. etc.
This would store each query in a sheet with the query name as the sheet name, it would then be simple to change the sheet names if required