#!/usr/env python

from os import listdir

from openpyxl import Workbook
from openpyxl import load_workbook

# Setup custom lambdas - Assigning lambdas to variables is considered bad practice, use functions
month_num = lambda file_name: int(file_name[-10:-8])
date_from_file = lambda file_name: file_name[-15:-5]

# Build file list
data_folder = 'N:\****\Trade Email\\'
file_names = [name for name in listdir(data_folder) if name.endswith('.xlsm') and int(name[-10:-8])>=3]

# Create our output workbook
wb_output = Workbook()

# Add 'SOTER' sheet in each file to our workbook
for idx,file_name in enumerate(file_names):
    print 'Processing File ' + str(idx + 1) + ' of ' + str(len(file_names)) # PEP 8 for whitespace

    ws_output = wb_output.create_sheet(date_from_file(file_name))

    wb_input = load_workbook(data_folder + file_name, read_only=True) # PEP 8 Whitespace
    ws_input = wb_input['SOTER']

    for row in ws_input.rows:
        for cell in row:
            ws_output[cell.coordinate].value = cell.value

wb_output.save('H:\Dump.xlsx')