import pandas as pd

from calendar import month_name
from pathlib import Path

filenames_array = [f'PB orders {m[:3]}.xlsb' for m in month_name[1:]]
masterfile_sheetname_array = [f'{m[:3]}data' for m in month_name[1:]]
my_path= Path("C:").resolve() / 'Load_attachments'



for filename in my_path.glob('*.xlsb'):
    print(filename)
    month = pd.read_excel(str(filename), sheet_name="Raw data ", engine="pyxlsb")
    print(month, end='\n-------------------------------------------------------------------\n')
    with pd.ExcelWriter(
            my_path.parent / 'PB report.xlsx',
            mode="a",
            if_sheet_exists="replace",
            engine="openpyxl",
    ) as writer:
        month.to_excel(
            writer,
            sheet_name=masterfile_sheetname_array[
                filenames_array.index(filename)
            ],  # Since the lists share the same indeces, use the filename to retrieve the name of the sheet
            engine="openpyxl",
            index=False,
        )