Merging excel workbooks into one using xlwings

Question

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:\Users\Callum\Desktop\env\AutoSettle.py", line 11, in <module>
    wb = xw.Book(excel_files)
  File "C:\Users\Callum\AppData\Local\Programs\Python\Python310\lib\site-packages\xlwings\main.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

Answer 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()