Results 1 to 2 of 2
2003-01-08, 14:27 #1
- Join Date
- Oct 2001
- Newport, Gwent, Wales
- Thanked 0 Times in 0 Posts
OutputTo Method Question (Access 97 Win 2k)
OK, I'm trying to export data from a query to Excel, the OutputTo method sorts me out nicely for this, so far so good. My question is this, can I specify a worksheet name for multiple queries going into the same Excel file? For example, query 1 is exported to query1.xls as sheet query 1, query 2 is exported to query1.xls as sheet query 2 etc.
I've had a look around in Access help, run a couple of searches on this forum and another, plenty of suggestions to use the OutputTo Method, but no one seems to have done what I want to do (this is probably because I'm just plain daft). The users who want the output in Excel can use the move / copy command in Excel, but I'm trying to make this a little more user friendly than that, if it can't be done, so be it, if it can then how????
2003-01-08, 16:12 #2
- Join Date
- Nov 2001
- Arlington, Virginia, USA
- Thanked 3 Times in 3 Posts
Re: OutputTo Method Question (Access 97 Win 2k)
You can use the TransferSpreadsheet method to export more than one query or table to same Excel workbook. Example:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "QRY1", "C:ACCESSTEST.XLS"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "QRY2", "C:ACCESSTEST.XLS"
This will export QRY1 & QRY2 to a workbook named TEST.XLS, with each query exported to a separate worksheet with same name as query. You may want to verify if Excel8 is correct spreadsheet format for version of Office you are using (Office 97 was version 8.0). Note that TransferSpreadsheet creates some pretty generic-looking spreadsheets; Output To at least preserves whatever formatting you may have defined in the table or query being exported (fonts, borders, column widthes, etc).