Output report to excel to a specified sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, can anyone help

I want to output a query to an excel spread sheet, which is fine, but i want
to specifie a sheet number in the spreed sheet for it to go i.e.

query 1 out put to excel (excel spread sheet name) in to sheet 1.
query 2 output to (same excel spread sheet) but to sheet 2

any help would be appreciated
 
For the first query, use OutputTo (to create the excel file), then for the
second use TransferSpreadsheet (this will append a worksheet to the same
excel file)
 
i love you thanks

SusanV said:
For the first query, use OutputTo (to create the excel file), then for the
second use TransferSpreadsheet (this will append a worksheet to the same
excel file)
Click to expand...
 
For the first query, use OutputTo (to create the excel file), then for the
second use TransferSpreadsheet (this will append a worksheet to the same
excel file)
Click to expand...

Actually, TransferSpreadsheet will also create a new Excel file if
none exists.
 
Fred -

How so? When I tried using transferspreadsheet for both outputs, the first
one errored "file not found" type error...

SusanV
 
Fred -

How so? When I tried using transferspreadsheet for both outputs, the first
one errored "file not found" type error...

SusanV
Click to expand...

Works fine for me.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"tblMileage", "C:\My Household\" & Format(Date, "mm-dd-yyyy") &
"Practice.xls", True

Creates a new spreadsheet named "12-22-2005Practice.xls" in the "My
Household" folder, with a worksheet named "tblMileage".
Run it again tomorrow and it will create another spreadsheet with
tomorrows date in the name.
 
My code is almost IDENTICAL to that including the insertion of the date in
the filename. Huh, weird... Maybe because I'm sending it to a server share
not a local folder or a version thing (I'm running A2K)
 
My code is almost IDENTICAL to that including the insertion of the date in
the filename. Huh, weird... Maybe because I'm sending it to a server share
not a local folder or a version thing (I'm running A2K)
Click to expand...

I'm running Access 2002 also..

This is copied from the TransferSpreadsheet action (Macro) help file.

Access creates a new spreadsheet when you export data from Access.
If the file name is the same as the name of an existing spreadsheet,
Access replaces the existing spreadsheet, unless you're exporting to
an Excel version 5.0 or later workbook. In that case, Access copies
the exported data to the next available new worksheet in the workbook.



Perhaps you can find a KnowledgeBase article about this regarding your
using a file on the Server, or maybe one of the other readers of this
post will have some ideas.
 
Back
Top