Let us assume I have two Excel files (source files) with sales data in it. All the files (or workbook) have the same data structure. I want to extract all the data from the source files and merge the data to a single (master) file. See the image below.
The Master file, which will extract and merge data from all the files, should be in another folder. You can modify it further according to your requirement.
You need to write the Macro (or the code) in the Master file, as it will extract data from other files and write the data in it.
I have a Button on my master file. The button is an ActiveX control. The click event calls a functions to read all the files in a folder.
Option Explicit Private Sub CommandButton1_Click() readAndMergeData End Sub Sub readAndMergeData() On Error GoTo ErrHandler Application.ScreenUpdating = False ' Our File System Objects. Dim objFs As Object Dim objFolder As Object Dim file As Object Set objFs = CreateObject("Scripting.FileSystemObject") Set objFolder = objFs.GetFolder("D:\somefolder\sample") ' The path for the files. ' The starting row. ' We'll update this variable after it reaches the Last Row of the previous file. Dim iStartRow As Integer iStartRow = 0 ' Loop through all the files in the folder. For Each file In objFolder.Files Dim src As Workbook ' The source workbook. Set src = Workbooks.Open(file.Path, True, True) Dim iTotalRows As Integer ' Get the total Used Range rows in the source file. iTotalRows = src.Worksheets("sheet1").UsedRange.Rows.Count Dim iTotalCols As Integer ' Get the total Columns in the source file. iTotalCols = src.Worksheets("sheet1").UsedRange.Columns.Count Dim iRows, iCols As Integer ' Now, read the source and copy data to the master file. For iRows = 1 To iTotalRows For iCols = 1 To iTotalCols Cells(iRows + iStartRow, iCols) = src.Worksheets("Sheet1").Cells(iRows, iCols) Next iCols Next iRows iStartRow = iRows + 1 iRows = 0 src.Close False Set src = Nothing Next ErrHandler: Application.EnableEvents = True Application.ScreenUpdating = True End Sub
I am creating an object of FSO.
Set objFs = CreateObject("Scripting.FileSystemObject")
Next, an object to read the folders
Set objFolder = objFs.GetFolder("D:\somefolder\sample")
As I have said before, the name of the files can be anything. However, the structure of each files must be same.
I’ll loop through each files in the folder. Using the File path, I am opening each workbook (the Excel file) one by one to read the data from it.
Set src = Workbooks.Open(file.Path, True, True)
Now, with the source file opened, I’ll get the total rows and columns from the file. Look at the UsedRange property. I don’t want to loop unnecessary through all the rows and columns.
iTotalRows = src.Worksheets("sheet1").UsedRange.Rows.Count iTotalCols = src.Worksheets("sheet1").UsedRange.Columns.Count
That’s it. Now read each cell’s value in the source and write it to the Master file.
Thanks for reading. ☺