VBA Code for merging Excel spreadsheets with differing columns

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 2 lines of interest stop Excel from updating which we’re working and set a flag to indicate that the first line of each spreadsheet contains the headers:

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):

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

The code then loops through the columns in the source spreadsheet and uses the mapping to copy all the values from the column in the source spreadsheet into the appropriate column in the output:

These lines select the column and rows in that column to copy:

And these lines select column and rows in that column to paste the values in the output:

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.