Saving Access tables to different sheets in the same workbook

[AT76]

I have different access tables and I need to save them to an excel spreadsheet but in different sheets. The way I know how to save a table to a spreadsheet is:

DoCmd.OutputTo acOutputTable, "tableName", acFormatXLS, "workbook.xls", False

I'm not sure though how to save to a specific Sheet. Could someone help me out?

Thank you!

 

[JerryKlmns]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myTable1, "c:\SystemTables.xls", False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myTable2, "c:\SystemTables.xls", False

Or create an array with all table names you need

Sub ExportMyTables

Dim myTableArray(1 To 50) As String
Dim iCount as Long

myTableArray(1)="Table1"
.
.
.
myTableArray(50)="Table50"
For iCount= 1 To 50
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myTableArray(iCount), "c:\SystemTables.xls", False
Next iCount

End Sub

 

[AT76]

Jerry,

Thanks for your help...

I'm getting the following error when trying to run your example:

Run-time error '3011': The Microsoft Jet database could not find the object 'TableName'. Make sure th object exists and that you spell its name and the path name correctly.

I have verified and the name and path are both correct. Any ideas why I would get this error message. By the way, the table does exist.

Thanks!

 

[AT76]

Jerry,

My apologies, your suggestion works GREAT!

Thank you!