Exporting Multiple files to One Excel Spreadsheet from Access

[Allan Busby]

Hi

I have read all the posts in the group in reference to the aforementioned
issue. However, I still cannot seem to be able to create a macro that allows
me to do this task.

I have many queries within my database that looks at performance of my team.
I will name two of these they are:

Qry Scheduled Jobs
Qry Completed Jobs

Now all I would like to do is have both these files exported into one excel
file but on different spreadsheets. When I creat a Macro that outputs file
to and try to incourage this I get an error appear about an OLE Server that
needs reregistering.

Can someone help me out with this please

Regards

 

[Ken Snell]

Use consecutive TransferSpreadsheet actions in a macro (or
DoCmd.TransferSpreadsheet in VBA code) to do this, one for each query. So
long as you always use the same filename, each query or table being exported
will go onto separate worksheets in this file.

 

[onedaywhen]

Or use the same path in the queries e.g.

SELECT *
INTO [Excel 8.0;Database=C:\MyWorkbook.xls].[A]
FROM A

SELECT *
INTO [Excel 8.0;Database=C:\MyWorkbook.xls].
FROM B