Add-in To Merge Multiple Workbooks
Ron de Bruin has posted a free Add-in for Excel here, which can be used to merge multiple Excel workbooks. Download the file RDBMerge.xlam, and then in Excel go to File . . . Options . . . Add-ins. In the Manage drop down menu at the bottom, select 'Excel Add-ins', and then browse to the downloaded file, and add it.
RDB Merge will be added as an option on the Data tab on your Excel ribbon. You will be able to select a folder containing multiple Excel workbooks. Select the format of those workbooks (.xls; .xlsx; or .csv). Ron's add-in givens you the option to select specific files, or merge all of the files in the folder. Specific worksheet names can also be targeted. The 'Use the sheet index' option gives you the ability to select one of the sequential worksheets in each workbook - the first, second, third, and so on.
A specific range of cells must be designated. By default, RDB Merge will add the path to each source workbook in column A.
Unfortunately the add-in doesn't line up columns which have matching headings.