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

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

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)

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.