Win32Com Save As Variable Name in Python 3.6

Question

I'm trying to loop through .xlsb file types in a folder and convert them to .csv in Python 3+ and Windows 10 and have pieced together the code below with help from SO. I want to save the new .csv as the original .xlsb name but am having issues - I have this so far:

import os
import glob
import win32com.client

path = r'C:\Users\folder\Desktop\Test Binary'
all_files_test = glob.glob(os.path.join(path, "*.xlsb"))
for file in all_files_test:
    excel = win32com.client.Dispatch("Excel.Application")
    excel.Visible = False
    doc = excel.Workbooks.Open(file)
    doc.SaveAs(Filename="C:\\Users\\folder\\Desktop\\Test Binary\\file.csv",FileFormat = 6) #overwrites file each time, need to substitute 'file'
    doc.Close(True)
    excel.Quit()
excel.Quit()

Which of course just overwrites each new iteration each time as 'file.csv'. How can I substitute the .xlsb name for each .csv name to SaveAs separate files? Thanks in advance.

Answer 1

Simply use str.replace on file variable to change extension. And consider wrapping in try/except to cleanly release COM objects regardless of error or not.

path = r'C:\Users\folder\Desktop\Test Binary'

all_files_test = glob.glob(os.path.join(path, "*.xlsb"))

for file in all_files_test:    
    try: 
        excel = win32com.client.Dispatch("Excel.Application")
        excel.Visible = False
        doc = excel.Workbooks.Open(file)

        csv_name = file.replace('.xlsb', '.csv')

        doc.SaveAs(Filename = csv_name, FileFormat = 6)
        doc.Close(True)
        excel.Quit()

    except Exception as e:
        print(e)

    finally:    
        doc = None
        excel = None

And to go one level deeper use combination of os.path.basename and os.path.join:

path = r'C:\Users\folder\Desktop\Test Binary'

...
csv_name = os.path.basename(file).replace('.xlsb', '.csv')

doc.SaveAs(Filename = os.path.join(path, 'Conversion_Files', csv_name), FileFormat = 6)