Export Access Tables to Excel Workbook
 

Hello,

I have two tables in Ms Access, which I want to export using VBA to an excel
workbook.

I know that I can export a table in Ms Access to an Excel workbook using the
command:
DoCmd.OutputTo acOutputTable, "Table_Name", acFormatXLS, "C:\myfile.xls"

Is there a method to export the second table to the same workbook on the
second worksheet? I have been able to do that only using some long method,
which is, opening the workbook, adding a worksheet, selecting it, looping
through the table's fields and writing them down to specific cells, and then
looping through the data in the table, all of which is manual, unlike the
code above.

Will appreciate any input,

Thanks!

Tj

Export Access Tables to Excel Workbook
 

A little VBA code will open both output files, activate the second, then move
the worksheet to the first file. Move the entire sheet, rather than
cell-by-cell. (You can record most of this by turning on the macro recorder
and going through the steps yourself. To move the sheet, right-click on the
worksheet tab and select 'Move or Copy', then choose the other workbook from
the drop-down.)

"Tj" wrote:

Hello,

I have two tables in Ms Access, which I want to export using VBA to an excel
workbook.

I know that I can export a table in Ms Access to an Excel workbook using the
command:
DoCmd.OutputTo acOutputTable, "Table_Name", acFormatXLS, "C:\myfile.xls"

Is there a method to export the second table to the same workbook on the
second worksheet? I have been able to do that only using some long method,
which is, opening the workbook, adding a worksheet, selecting it, looping
through the table's fields and writing them down to specific cells, and then
looping through the data in the table, all of which is manual, unlike the
code above.

Will appreciate any input,

Thanks!

Tj