Open, Save, then Close Excel files in Python-CodePudding

I'm attempting to loop through about 300 xls files. The goal is to open, save, then close each one. I have the following so far:

files = glob.glob("C:/Users/cmoore/Excel Files/*.xls")

xl = win32com.client.DispatchEx("Excel.Application")
xl.DisplayAlerts = False

for wb in files:  
    xl.Workbooks.Open(wb)
xl.Visible = False
xl.Close(wb)
xl.Quit()

The problem is that it doesn't cycle through all of the files. There are usually about 5 that dont get touched. I then get the following error after the code finished running:

"ret = self.oleobj.InvokeTypes(1923, LCID, 1, (13, 0), ((8, 1), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17)),Filename pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Open method of Workbooks class failed', 'xlmain11.chm', 0, -2146827284), None)"

Any thoughts?

Thanks for your help!

CodePudding user response：

You are opening the file inside the loop and closing it outside. For 300 opens you have one close. Try pushing close inside the loop

Inside the loop, close the workbook not xl object.

for wb in files:  
    xl.Workbooks.Open(wb)
    xl.Visible = False
    wb.Close(True)

CodePudding user response：

Try to put xl.Close(wb) inside the for loop.