Python use case – Save each worksheet as a separate excel workbook

In this post “Python use case – Save each worksheet as a separate excel workbook“, we are going to learn that how we can create a separate workbook for each worksheet of a given excel file. We will be copying data, values, formatting and all other settings of the sheet in the newly created workbook. Assume that we have an excel workbook having five sheets named “Sheet1“, “Sheet2“, “Sheet3“, “Sheet4“, and “Sheet5” which is as this.

Now, we want to create 5 different excel workbooks (one for each worksheet).

We are going to use Excel application to copy data, values, formatting and other settings of each sheet as a new workbook in a python script. Below is the python code.

def create_wb_from_ws():
    try:
        filepath = 'D:\Test\Excel\Sample Excel.xlsx'
        
        from win32com.client import DispatchEx
        excel = DispatchEx("Excel.Application")

        if excel == None:
            print('-' * 100)
            print('Error: Excel is not found on this machine. Existing!')
            print('-' * 100)
            return
        else:
            print('-' * 100)
            print('Message: Excel version {0} is available.'.format(excel.version))
            print('-' * 100)
        
        if int(float(excel.version)) < 12:
            fileext = '.xls'
        else:
            fileext = '.xlsx'

        import os
        if not os.path.exists(filepath):
            print('The entered file path does not exists. Existing!')
            return

        filedir = os.path.join(os.path.dirname(filepath), os.path.splitext(os.path.basename(filepath))[0])
        if not os.path.exists(filedir):
            os.mkdir(filedir)

        excel.Visible = False
        excel.DisplayAlerts = False
        wb = excel.Workbooks.Open(Filename = filepath)
        wb.Application.Visible = False

        for sheet in wb.Worksheets:
            filename = os.path.join(filedir, sheet.name + fileext)
            wbnew = excel.Workbooks.Add()
            wbnew.Application.Visible = False
            sheet.Copy(Before = wbnew.Worksheets(1))

            for s in wbnew.Worksheets:
                if s.name != sheet.name:
                    wbnew.Worksheets(s.name).Delete()

            wbnew.SaveAs(filename)
            print('Saved sheet name "{0}" as a new excel file at {1}'.format(sheet.name, filename))
            wbnew.Close(SaveChanges = 1)

        wb.Close(True)
        excel.Quit()
    except:
        print('-' * 100)
        print('Error occurred')
        print('-' * 100)
        raise

if __name__ == "__main__":
    create_wb_from_ws()

To execute the above python script, we can call this .py file using command prompt window as this.

python d:\Code\ExcelCreator.py

The output will be as this:

Thanks for the reading. Please share your input in comment section.