export query data to excel in a specific worksheet.

  • Thread starter Thread starter OTWarrior via AccessMonster.com
  • Start date Start date
O

OTWarrior via AccessMonster.com

I want to do a report of some of the data on the system, and I have created
various reports.

I have figured out how to export the data to an existing excel spreadsheet
using:
DoCmd.OutputTo acOutputQuery, SelQuery, acFormatXLS, sXL

&

DoCmd.TransferSpreadsheet acExport

however, I would like to be able to export multiple queries to the same
workbook within the spreadsheet.

is this possible?

Plus, how do you name the worksheet to something over than the query name?
can this be done in the export?
 
I want to do a report of some of the data on the system, and I have created
various reports.

I have figured out how to export the data to an existing excel spreadsheet
using:
DoCmd.OutputTo acOutputQuery, SelQuery, acFormatXLS, sXL

&

DoCmd.TransferSpreadsheet acExport

however, I would like to be able to export multiple queries to the same
workbook within the spreadsheet.

is this possible?

Plus, how do you name the worksheet to something over than the query name?
can this be done in the export?
Click to expand...

If you use the TransferSpreadsheet method each time you export, a
worksheet will be named the same as the query (if that worksheet does
not already exist). So if you export 3 different tables/queries to
the same spreadsheet, you will have 3 different worksheets named in
the spreadsheet. However, if that worksheet name already exists, then
the new data will over-write the old.
 
fredg said:
If you use the TransferSpreadsheet method each time you export, a
worksheet will be named the same as the query (if that worksheet does
not already exist). So if you export 3 different tables/queries to
the same spreadsheet, you will have 3 different worksheets named in
the spreadsheet. However, if that worksheet name already exists, then
the new data will over-write the old.
Click to expand...

What I originally meant is can you reference the same worksheet for access to
export to, but put the data below the data you just have (or even to the side)


I have tried using
"qry_exp_query1" & "qry_exp_query2"
and
"qry_exp_query1" and "qry_exp_query2"
and it just comes up as a type mismatch

What I am also trying to do is to NAME the worksheet i am copying to / have
copied to, rather than have the name of the query showing.
 
Back
Top