export query data to excel in a specific worksheet.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

OTWarrior - 28 Jun 2007 17:04 GMT

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?

Reply to this Message

fredg - 28 Jun 2007 17:30 GMT

> I want to do a report of some of the data on the system, and I have created
> various reports.
[quoted text clipped - 14 lines]
> Plus, how do you name the worksheet to something over than the query name?
> can this be done in the export?

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.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

OTWarrior - 29 Jun 2007 08:24 GMT

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

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.

Reply to this Message