Parse through multiple excel files in a folder hierarchy and consolidate Using win32com library so that sheets get copied with formatting intact

Consolidate_excel_sheets_with formatting

# -*- coding: utf-8 -*-
"""
Created on Tue Apr 16 15:45:52 2019

@author: Vinoth_Subramanian
Parse through multiple excel files in a folder heirarchy and consolidate
Using win32com library so that sheets get copied with formatting intact
"""

import os
from win32com.client import Dispatch

target_path = os.getcwd()

xl_name = "Consolidated.xlsx"

xl = Dispatch("Excel.Application")
xl.Visible = True # to see the file getting updated real time

target_wb = xl.Workbooks.Open(Filename = target_path + "\\" + xl_name)
   
for path, dirs, files in os.walk(target_path):
    for f in files:
        
        curr_path = path
        
        f_name, extn = os.path.splitext(f)
                
        if (extn == ".xlsx"and 
            f_name.split()[-1].lower() == "model" and 
                f_name.split()[-2].lower() == "commercial"):
            
                    account = f_name.split()[0]
                    print(account)
                    print(curr_path)
                    
                    source_wb = xl.Workbooks.Open(Filename=curr_path + "\\" + f)
                    
                    source_ws = source_wb.Worksheets(1)
                    source_ws.Name = account # rename the sheet to match account name
                    source_ws.Copy(Before=target_wb.Worksheets(1))
                    
                    source_wb.Close(SaveChanges=False)
                
target_wb.Close(SaveChanges=True)

xl.Quit()