Export Queries to Excel

[sally t]

Hi, can anyone help me. I want to export the results of
SEVERAL Access 2000 Queries to ONE Excel Workbook -
consolidating this information from the queries. I know I
can export one at a time, but is there a way to export
several into one Workbook? Many thanks.

Sally t

 

[Ken Snell]

Use a macro or VBA code and use the TransferSpreadsheet command. If you
specify the name of an EXCEL file that already exists, ACCESS adds a new
worksheet to that file and exports the query's results onto that new
worksheet.

 

[sally t]

Thank you.

Tried a macro with Transfer Spreadsheet which works great -
can add multiple queries to it to export all at once.
However, would like to to append the data in the SAME
worksheet in Excel and instead it creates new, separate,
worksheets for each Query exported. Do you know if I can
achieve this? Thank you so much.
Sall

 

[Ken Snell]

As I stated in my post, TransferSpreadsheet creates new worksheets for each
query. TransferSpreadsheet will not add data to an existing sheet.

To do what you wish, you would need to do one of the following:

(1) Assuming that all the queries have the same number of fields, that the
fields are in the same format for each corresponding field in each query,
and that the fields are all in the same order for each query, you can create
a union query that selects records from each query into one query, and then
export that union query's results.

(2) Use VBA code to open the EXCEL file via automation, open each query in a
recordset, write the results of each query's recordset into the EXCEL file's
sheet via automation, and continue this for each query.

 

[John Nurick]

Hi Sally,

If you want to export data from multiple Access queries into a single
table on an Excel worksheet, the thing to do is to create one new query
that returns the data from all the others, and export that.

Probably this will be a Union query along these lines
SELECT * FROM FirstQuery
UNION
SELECT * FROM SecondQuery
UNION
SELECT * FROM ThirdQuery
ORDER BY SomeField;

If you want to create multiple separate tables on a single worksheet
(with empty space between them and potentially with different numbers
and types of fields, their own column headings and titles, and so on),
you'll need to use VBA and Automation to control Excel from Access.