#Import a cell from multiple excel worksheet files.
#12/17/2015

import os
import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook

index=1

wb = Workbook()
ws = wb.active
ws.title = "Tray List Import"

dest_filename = 'P:/Python Scripts/xlsx cell grabber/Traylist.xlsx'

path= 'T:/Docs/SITT/ElPasoSurgicalSpecialtyHospital/CountSheets/'


for root,dirs,files in os.walk(path):
    xlsfiles=[ _ for _ in files if _.endswith('.xlsx') ]
    for xlsfile in xlsfiles:
        ewb = openpyxl.load_workbook(os.path.join(root,xlsfile))
        n = len(wb.sheetnames)
        

        for s in range(n):
            #base = os.path.basename(os.path.join(root,xlsfile)) #for sheets with tray name in filename
            #trayname = os.path.splitext(base)[0] #Goes with the base variable, this will spit out the trayname from the file.
            sheet = ewb.worksheets[s]
            data1 = sheet['A1'].value
            ws['B' + str(index)] = data1
            ws['A' + str(index)] = xlsfile
            
            
        index=index+1

ws.column_dimensions['A'].width = 40
ws.column_dimensions['B'].width = 60
wb.save(dest_filename)