Summarise information from a set of excel files into a summary sheet

summarise.py

import os
import xlwings as xw


# Setup the application
app = xw.App(visible=False)
app.display_alerts = False

# Create a new workbook
new_wb = app.books.add()
# Worksheet is Sheet 0 of New Workbook
new_ws = new_wb.sheets[0]

# Headers for the new workbook
headers = ["Header1", "Header2", "Header3"] # <-- List of header in the summary sheet
new_ws.range('A1').value = headers

# Directory path
folder_path = r"Source folder of excel sheets to summarize"
new_row = 2

# Loop through each file in the folder
for file in os.listdir(folder_path):
    if file.lower().endswith(''.xlsm', '.xls','.xlsx'):
        file_path = os.path.join(folder_path, file)
        src_wb = xw.Book(file_path)  # Open the source workbook

        # Copying values
        for i in range(2):
            new_ws.range(f'A{new_row}').value = src_wb.sheets['Sheet1'].range('B2').value <-- Header1 Value source
            new_ws.range(f'B{new_row}').value = src_wb.sheets['Sheet1'].range('B4').value <-- Header2 Value source
            new_ws.range(f'C{new_row}').value = src_wb.sheets['Sheet1'].range('B5').value <-- Header3 Value source

        src_wb.close()  # Close the source workbook

# Save the new workbook
new_wb.save(r'Target Folder/summary.xls') <-- Save summary sheet
new_wb.close()

# Quit the application
app.quit()