Combine files into one Excel Sheet using Access VBA

[vishpuri]

Is there a way i can combine Multiple Excel workbooks into One Excel Workbook using Access VBA. Something like when a user clicks on a button in access it will generate excel files and combine them to one excel workbook with different worksheets in it. TIA

[Luuk]

Yes you can to that in Excel.
first create two EXCEL-sheets, and than combine them using Excel

Function Macro1()
On Error GoTo Macro1_Err
    DoCmd.OutputTo acOutputTable, "Tabel1", "ExcelWorkbook(*.xlsx)", "d:\temp\tabel1.xlsx", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputTable, "Tabel1", "ExcelWorkbook(*.xlsx)", "d:\temp\tabel2.xlsx", False, "", , acExportQualityPrint
 
    Dim MyXL As Object
 
    Set MyXL = CreateObject("Excel.Application")
    With MyXL
        .Workbooks.Open "d:\temp\tabel1.xlsx"
        .Workbooks.Open "d:\temp\tabel2.xlsx"
 
        .Workbooks("tabel2.xlsx").Sheets("Tabel1").Select
        .Workbooks("tabel2.xlsx").Sheets("Tabel1").Name = "Tabel2"
        .Workbooks("tabel2.xlsx").Sheets("Tabel2").Copy After:=.Workbooks("tabel1.xlsx").Sheets(1)
        .Workbooks("tabel2.xlsx").Close SaveChanges = False
        .Application.Visible = True
    End With
 
Macro1_Exit:
    Exit Function
 
Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit
 
End Function
 
 
 

Of course you should delete "Tabel2.xlsx", but that's up to you ...