By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,222 Members | 1,730 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,222 IT Pros & Developers. It's quick & easy.

Combine files into one Excel Sheet using Access VBA

P: 1
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
Feb 7 '19 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 1,035
Yes you can to that in Excel.
first create two EXCEL-sheets, and than combine them using Excel

Expand|Select|Wrap|Line Numbers
  1. Function Macro1()
  2. On Error GoTo Macro1_Err
  3.     DoCmd.OutputTo acOutputTable, "Tabel1", "ExcelWorkbook(*.xlsx)", "d:\temp\tabel1.xlsx", False, "", , acExportQualityPrint
  4.     DoCmd.OutputTo acOutputTable, "Tabel1", "ExcelWorkbook(*.xlsx)", "d:\temp\tabel2.xlsx", False, "", , acExportQualityPrint
  5.  
  6.     Dim MyXL As Object
  7.  
  8.     Set MyXL = CreateObject("Excel.Application")
  9.     With MyXL
  10.         .Workbooks.Open "d:\temp\tabel1.xlsx"
  11.         .Workbooks.Open "d:\temp\tabel2.xlsx"
  12.  
  13.         .Workbooks("tabel2.xlsx").Sheets("Tabel1").Select
  14.         .Workbooks("tabel2.xlsx").Sheets("Tabel1").Name = "Tabel2"
  15.         .Workbooks("tabel2.xlsx").Sheets("Tabel2").Copy After:=.Workbooks("tabel1.xlsx").Sheets(1)
  16.         .Workbooks("tabel2.xlsx").Close SaveChanges = False
  17.         .Application.Visible = True
  18.     End With
  19.  
  20. Macro1_Exit:
  21.     Exit Function
  22.  
  23. Macro1_Err:
  24.     MsgBox Error$
  25.     Resume Macro1_Exit
  26.  
  27. End Function
  28.  
  29.  
  30.  
Of course you should delete "Tabel2.xlsx", but that's up to you ...
Feb 9 '19 #2

Post your reply

Sign in to post your reply or Sign up for a free account.