Copy a couple of sheets from excel to one workbook from access

[zrazzaq]

Hi:
From access, I wrote code to import into 3 separate excel files. I customized it so I am not using docmd.transferspreadsheet option. More of creating an excel file and custonizing the report.
Now what I need to do is copy all three of those files the exact format and place it into on workbook. Does anyone know how to do that from Access VBA?
Thanks
Z

 

[DatabaseDude]

Here's an example for doing one ... modify for doing multiple instances ... and of course change file names/paths and worksheet names to suit your needs.

Note: Be sure to set a reference to Excel in your Access VBA IDE.

    ' Original workbook
    Excel.Workbooks.Open Filename:= _
        "C:\Book1.xls"
    ' Destination workbook
    Excel.Workbooks.Open Filename:= _
        "C:\Book4.xls"
    ' Copy sheet from original workbook as first sheet in destination workbook
    Excel.Workbooks("Book1").Sheets("Sheet1").Copy Before:=Excel.Workbooks("Book4").Sheets(1)