greenbcz
06-10-2017, 02:57 AM
Hi everyone!
I need to create an Excel macro using VBA which can merge more than one excel files keeping headers, columns and sheets.
Two files are attached as examples which need to be merged into one. Format of both files is same.
Every week I get such new file so I need a Macro to add data of the file to previous ones and hence final file at time of running macro should include data of all files merged together.
I have been able to create following code but unable to proceed further successfully.
Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
Any help or guidance will be appreciated in this regard !
I need to create an Excel macro using VBA which can merge more than one excel files keeping headers, columns and sheets.
Two files are attached as examples which need to be merged into one. Format of both files is same.
Every week I get such new file so I need a Macro to add data of the file to previous ones and hence final file at time of running macro should include data of all files merged together.
I have been able to create following code but unable to proceed further successfully.
Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
Any help or guidance will be appreciated in this regard !