How can I Export a Table in Access using VBA into a specific sheet in an Excel spreadsheet?

Question

I have a some tables, we will call them Table1,Table2.... and I need them to be Exported into specific spreadsheets in a macro enabled Excel File (.xlsm) that already exists. So I would need to put Table1 into Sheet2, Table2 into Sheet3... and so on. I had been doing this manually by going to the export menu in Access but it is getting monotonous so I would like to automate the process. The Excel file will already have code in each spreadsheet which would need to still be intact.

Answer 1

You can use the Excel object model to do this.

Open a module up, then go to Tools / References. Scroll down and choose the one for MS Excel.

In your code, you can simply create a new workboook like any other object. Here is sample code OTTOMH

Dim ExcelApp as Excel.Application
Dim ExcelWorkbook as Excel.Workbook
Dim WorkSheet as Excel.Worksheet

Set ExcelApp = new Excel.Appliation

ExcelWorkbook = ExcelApp.Workbooks.Add()

ExcelWorkbook.Title = "All Sales"
ExcelWorkbook.Subject = "Sales"

ExcelWorkbook.Sheets(0).Activate
set WorkSheet = ExcelWorkbook.Sheets(0)

Dim Counter As Integer
Dim curCell

For Counter = 1 To 20
    Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
    If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
Next Counter

ExcelWorkbook.SaveAs Filename:="Allsales.xls"