Search code examples
pythonxlsxxls

How to convert multiple files of the same extension?


I am using this code to convert ".xls" file into ".xlsx". I was successful in doing it for 1 file. But I am trying to put this in a loop so that I can convert multiple files at once.

The files have different names, so it is getting difficult for me to achieve this. This is my code :

import win32com.client as win32
fname = "C:\\Users\\Desktop\\test\\a.xls"
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(fname)

wb.SaveAs(fname+"x", FileFormat = 51)    #FileFormat = 51 is for .xlsx extension
wb.Close()                               #FileFormat = 56 is for .xls extension
excel.Application.Quit()

Any help much appreciated. Thank you. Edit-1 : Implemented 1st solution by @Ayush, following error observed :

Traceback (most recent call last):
  File "C:\Users\Desktop\convert2.py", line 7, in <module>
    wb = excel.Workbooks.Open(file)
  File "C:\Users\AppData\Local\Temp\gen_py\3.9\00020813-0000-0000-C000-000000000046x0x1x9\Workbooks.py", line 75, in Open
    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', "Sorry, we couldn't find a.xls. Is it possible it was moved, renamed or deleted?", 'xlmain11.chm', 0, -2146827284), None)

Solution

  • You can use the os module to get the list of the files in the specified folder and loop through them. Try this and let me know if it worked.

    import win32com.client as win32
    import os
    path = "C:\\Users\\Desktop\\test"
    dir_list = os.listdir(path)
    for file in dir_list:
        file_with_path = os.path.join(path, file)
        excel = win32.gencache.EnsureDispatch('Excel.Application')
        wb = excel.Workbooks.Open(file_with_path)
    
        wb.SaveAs(file_with_path+"x", FileFormat = 51)    #FileFormat = 51 is for .xlsx extension
        wb.Close()                               #FileFormat = 56 is for .xls extension
        excel.Application.Quit()
    

    Edit:

    Note: Use the above code if you want to edit those excel files too otherwise if you just want to rename the files use the code below

    import os
    path = "C:\\Users\\Desktop\\test"
    for file in os.listdir(path):
        file_with_path = os.path.join(path, file)
        os.rename(file_with_path, file_with_path + 'x')
    

    Note: The above code can be SLIGHTLY improved again but then you have to make sure that the below script is in the same directory as the excel files

    import os
    [os.rename(file, file + 'x') for file in os.listdir('.')]