import win32com.client
from pathlib import Path

class excel_macro:
    excel_format = { '.csv': 6, '.xls': 56 , '.xlsx': 51 }

    def __init__(self):
        self.excel = win32com.client.Dispatch("Excel.Application")
        self.excel.DisplayAlerts = False
        #excel.Visible = True


    def split(self, file, format = None): 
        filename = Path(file)
        path = str(filename.parent.resolve())
        print(filename.resolve())

        if format == None:
            ext = filename.suffix
        else:        
            ext = "." + format

        wb = self.excel.Workbooks.Open(filename.resolve())

        for sh in wb.Sheets:
            tab_filename = filename.stem + "_" + sh.Name + ext
            print(" --> " + tab_filename)
            wb2 = self.excel.Workbooks.Add()
            wb.Worksheets(sh.Name).Activate()
            ws = self.excel.ActiveSheet
            ws.Copy(Before=wb2.sheets(1))
            wb2.sheets(2).Delete()
            wb2.UpdateLinks = 2
            wb2.SaveAs(Filename=path + "\\" + tab_filename, FileFormat=self.excel_format[ext])
            wb2.Close(True)

        wb.Close(True)
    

if __name__ == "__main__":
    excel_macro().split("Excel\\Mappe1.xlsx", "csv")