python - Copying data from multiple excel files to multiple sheets of a single excel file

Question

I am trying to copy data from multiple excel files into multiple sheets of a single excel file. But I am not able to figure out how to change the active sheet of the master excel so that I can copy the new data into a new sheet. I can do this by repeating the code multiple times but that is not feasible. Also the below code is one of the approaches that I tried but this just rewrites data into the same sheet, so if you can help me to change the active sheet of the master excel or if you have a better solution that would be helpful, preferred in openpyxl instead of pandas or xlrd

`import openpyxl as xl
    excel1 = "excel1.xlsx"
    excel2 = "excel2.xlsx"
    masterexcel = "Master excel.xlsx"
    files = [excel1, excel2]
    for file in files:
        wb1 = xl.load_workbook(file)
        ws1 = wb1.worksheets[0]

        filename1 = masterexcel
        wb2 = xl.load_workbook(masterexcel)
        ws2 = wb2.worksheets[0] #this is to change the active sheet
        wb2.active = 0
    
        mr = ws1.max_row
        mc = ws1.max_column
        
        for i in range(1, mr + 1):
            for j in range(1, mc + 1):
               
                c = ws1.cell(row=i, column=j)
               
                ws2.cell(row=i, column=j).value = c.value
      
        wb2.save(str(masterexcel))`

question from:https://stackoverflow.com/questions/66066454/copying-data-from-multiple-excel-files-to-multiple-sheets-of-a-single-excel-file

Answer 1

Waitting for answers