Btukfyl

xlDoc =  win32com.client.gencache.EnsureDispatch("Excel.Application")

writefile=xlDoc.Workbooks.Open("M:Python automationMain.xlsx")

count = writefile.Sheets.Count

print "count", count

Sheetcount=count-1

for i,sht in enumerate(writefile.Sheets):

    print "i",i

    if i==0:

        print ""

    else:

        xlDoc.Worksheets(sht.Name).Activate()

        xlSht = xlDoc.ActiveSheet

        name=xlSht.Name

        filename =os.path.join(dir,name+".xlsx")

        print filename

        key=i+1

        data[key]= ReadData(filename)





#To read data from excel 

def ReadData(filename):

    xlApp = Dispatch("Excel.Application")

    xlApp.Quit()                                        #Close excel sheet if open

    xlApp.Visible=False

    ##xlWb = xlApp.Workbooks.Open(filename)

    print "Reading "

    DataTemp = 

    xlWb = xlApp.Workbooks.Open(filename)

    Sheet= xlWb.Sheets(1)

    used = Sheet.UsedRange

    nrows = used.Row + used.Rows.Count - 1

    ncols = used.Column + used.Columns.Count - 1

    while (Sheet.Cells(nrows,1).Value) != None:

            nrows += nrows

    #Read all data 

    DataTuple = Sheet.Range("A2:N%d" % nrows).Value

    DataList = [list(x) for x in DataTuple]

    for rowA in DataList:

            for n,i in enumerate(rowA):

                    if i == None:

                            rowA[n] = ''

            DataTemp.append(rowA)

    xlWb.Close(True)

    xlApp.Application.Quit()

    return DataTemp

I have to read the sheet names of the main excel file and use the names to read the corresponding excel files in the folder and store the data into a dictionary. My current code does not read any more excel files after reading the first excel file.

The main excel file has 4 sheets but only goes on to reading the second sheet "Bon" and then stops.

Output:

PS M:Python automation>python try.py

count 4

i 0

i 1

M:Python automationBon.xlsx

Reading

PS M:Python automation>