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 name of the source files can be anything. However, please make sure that all the files (except the master file) are in same folder.
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() ReadAndMerceData End Sub Sub ReadAndMerceData() 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
In the example above, I am using VBA FSO (or File System Object) model for accessing files in a folder. The FSO provides object-based tools for working with files and folders. This is one of the easiest ways to get to know everything about the files in a folder.
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.