Merging excel workbooks into one using xlwings

I am currently trying to merge a number of excel spreadsheets into one workbook to create a monthly master workbook.

I have written the following code to try and achieve this:

…

from pathlib import Path
import xlwings as xw

print("enter file directory")
SOURCE_DIR = input()

excel_files = list(Path(SOURCE_DIR).glob("*.xlsx"))
combined_wb = xw.Book()

for excel_file in excel_files:
    wb = xw.Book(excel_files)
    for sheet in wb.sheets:
        sheet.api.copy(After=combined_wb.sheets[0].api)
    wb.close()

combined_wb.sheets[0].delete()
combined_wb.save(f("all_settlement_reports.xlsx"))

if len(combined_wb.app.books) == 1:
    combined_wb.app.quit()
else:
    combined_wb.close()

…

The first steps are fine, I am prompted for an input file path but then I get the following errors which have stumped me:

Traceback (most recent call last):
  File "C:UsersCallumDesktopenvAutoSettle.py", line 11, in <module>
    wb = xw.Book(excel_files)
  File "C:UsersCallumAppDataLocalProgramsPythonPython310libsite-packagesxlwingsmain.py", line 817, in __init__
    fullname = fullname.lower()
AttributeError: 'list' object has no attribute 'lower'

Can anyone help out with this as I am really struggling to fix the problem.

Thanks everyone

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

from pathlib import Path

import xlwings as xw

SOURCE_DIR = input("enter file directory")

excel_files = list(Path(SOURCE_DIR).glob("*.xlsx"))
combined = xw.Book()

app = combined.app
app.interactive = False
app.visible = False

reference_sheet = combined.sheets[0]
for file_name in excel_files:
    for sheet in xw.Book(str(file_name)).sheets:
        sheet.copy(before=reference_sheet)

reference_sheet.delete()
combined.save("all_settlement_reports.xlsx")
app.kill()

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0