Output report to excel to a specified sheet

[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

 

[SusanV]

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)

 

[Guest]

i love you thanks

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)

 

[SusanV]

<blush>
Your very welcome!

i love you thanks

 

[fredg]

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)

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

 

[SusanV]

Fred -

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

SusanV

 

[fredg]

Fred -

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

SusanV

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.

 

[SusanV]

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)

 

[fredg]

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)

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.