Export data to Excel

[polina]

Hi,

I was wondering if anyone knows how to export different tables to one excel workbook, putting each table into separate sheet within the book.

Thanks.

 

[Travis]

You can do this with the TransferSpreadSheet command.

 

[Travis]

Pat,

Using the docmd.TransferSpreadSheet you can send Tables/Queries to the Same SpreadSheet but a different WorkSheet.

Of course Each table/query has to have a different name or it will overwrite the existing one.

Example:

Public Sub TransToSpreadsheet()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Students", CurrentProject.Path & "\MySpreadSheet.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Students And Classes", CurrentProject.Path & "\MySpreadSheet.xls"

End Sub