VBA Code for combining multiple Excel files and merging rows

The code

What the code does

There are several subroutines and a function in the VBA code we have used. In some of the subroutines, you will see the following at the beginning:

and this at the end:

This will ensure that if an error occurs while the subroutine runs, it will be caught and the code after the ErrMsg: label will be executed which simply checks that there is an error (I.e. Err.Number is not 0) then shows a message with the description of the error.

The next 3 lines of interest stop Excel from updating which we’re working, set a flag to indicate that the first line of each spreadsheet contains the headers and specify the column name which contains the unique values in the output Excel spreadsheet:

Now, to the guts of the subroutine. We’re using the FileSystemObject to get the directory that contains the Excel files to merge:

We then assign the active worksheet in the master spreadsheet to a local variable for ease of use:

Next, we work out what the next row in the output spreadsheet is that we should insert rows at:

Then, we select each file in the folder and process it in a loop:

Within the loop, the code calls the MapColumns() function to get the mappings from the source spreadsheet columns to the equivalent output columns:

Then opens the spreadsheet in Read Only mode and loops over each worksheet in the workbook:

Within the loop, for each worksheet, we get the used data range (excluding the header row if there is one):

Then, when processing a worksheet, we need to determine which column in that worksheet contains the unique value (if any):

We also need to define the range in the output worksheet that will be searched to try and match the row:

The final thing to do is to try and find an existing row that matches the unique value from the input row. If we find one, we need to update the existing row, otherwise, we copy the row into a new row in the output.

The specific column map for the file currently being processed is retrieved using this code (see below for definition of MapColumns() ):

The MapColumns()  function

This function allows us to map the columns for each spreadsheet:

In this function, you can see a section for each Excel file we wish to merge, starting with a Case expression, e.g.  Case "ExcelFile1.xlsx" . Following the Case expression, the columns are mapped by adding an entry to a collection for each column in the source spreadsheet. The Key  represents the column in the source spreadsheet and the Item  represents the column in the output spreadsheet. For example, taking the last mapped column in source spreadsheet ExcelFile1.xslx:

Here we are mapping column F, email, of the source spreadsheet, ExcelFile1.xlsx, to column G, email, of the output spreadsheet.