import os
import glob

for root, dirs, files in os.walk("D:/Test"):
    for file in files:
        if file.endswith(".xlsx"):
             #print(os.path.join(root, file))
             s = os.path.join(root, file)
             print(s)
all_data = pd.DataFrame()
for f in s:
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)

# now save the data frame
writer = pd.ExcelWriter('result.xlsx')
all_data.to_excel(writer,'sheet1')
writer.save()

Traceback (most recent call last):

  File "<ipython-input-169-41c6d76207e7>", line 12, in <module>
    df = pd.read_excel(f)

  File "C:UsersUserAnaconda3libsite-packagespandasutil_decorators.py", line 118, in wrapper
    return func(*args, **kwargs)

  File "C:UsersUserAnaconda3libsite-packagespandasioexcel.py", line 230, in read_excel
    io = ExcelFile(io, engine=engine)

  File "C:UsersUserAnaconda3libsite-packagespandasioexcel.py", line 294, in __init__
    self.book = xlrd.open_workbook(self._io)

  File "C:UsersUserAnaconda3libsite-packagesxlrd__init__.py", line 116, in open_workbook
    with open(filename, "rb") as f:

FileNotFoundError: [Errno 2] No such file or directory: 'D'

paths = []
for root, dirs, files in os.walk("D:/Test"):
    for file in files:
        if file.endswith(".xlsx"):
             #print(os.path.join(root, file))
             s = os.path.join(root, file)
             print(s)
             paths.append(s)

all_data = pd.DataFrame()
for f in paths:
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)