How to Merge Data From Multiple Excel Files into a Single Excel File or Access Database?

Question

I have a few dozen excel files which are all of the same format (i.e. 4 worksheets per Excel file). I need to combine all the files into 1 master file which must have just 2 of the 4 worksheets. The corresponding worksheets from each Excel file are named exactly the same as are the column headers.

While each file is structured the same, the information within sheet 1 and 2 (for example) is different. So it can’t be combined into one file with everything in one sheet!

I've never used VBA before and I'm wondering where I might start this task!

Answer 1

Since you wanted them on one sheet, I think the above isn't what you were looking for.

I use Microsoft Access to merge data. Especially, if different sheets have a similar identifier (part number/contact person/ect).

You create a "table" for each spreadsheet to be merged You create a "query" which pulls the desired columns to one sheet

Answer 2

Please see the Consolidate Worksheets Wizard add-in for Excel

The add-in has several merge modes, one of them does exactly what you need.

Please see this link for a detailed description of the mode (how to combine sheets with the same name to one)

The add-in is a shareware, but it has a 15-day fully-funtional trial version (download button at the top of the page), so you can merge thousands of your workbooks for free :)

Answer 3

Today I came across this link RDBMerge, Excel Merge Add-in for Excel for Windows, which I think will serve the purpose.That is a free macro based tool.

Answer 4

Here is a solution in python.

import glob
import pandas as pd

path = "<path to files>"
file_identifier = "*.xlsx"

all_data = pd.DataFrame()
for f in glob.glob(path + "/*" + file_identifier):
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)

Answer 5

1. Open all of the Excel workbooks in the same instance of Excel.
   • You might have to click the internal "restore" button to see the individual workbooks.
2. Select all sheets you want to move by Ctrl+Clicking on the worksheet tabs.
3. Right click on one of the selected tabs, and choose Move or Copy...
4. In the dialog that pops up, select the destination workbook (your "Master" workbook) and then choose where to insert them.
   • The (move to end) option is likely what you want, but you can always reorder them later.
   • Choose Create a copy if you do not want the sheets to be removed from the first workbook
5. Click OK.

The selected worksheets will be moved or copied from the original workbook into your "Master" workbook. Just close the source wookbook and do it again with the next one, until you've collected all the worksheets you care about into one large wookbook. Make sure to save!