Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Newport, Gwent, Wales
    Thanked 0 Times in 0 Posts

    OutputTo Method Question (Access 97 Win 2k)

    Hello again

    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????



  2. #2
    Bronze Lounger
    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).


Posting Permissions

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