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

Parfait's answer is good, but has a few flaws. I have remedied those (that I have noticed) in this answer, and refactored out some context managers to make the logic easier to understand (and hence easier to modify).

It now prints failed files to sys.stdout (to let you recover, Unix-style, by replacing the for loop with repeated input() / f.readline()[:-1] calls), and only opens the Excel COM object once; this should be a lot faster.

I have also added support for recursively performing this match, but this feature requires Python 3.5 or above in order to work.

import os
import glob
import traceback
from contextlib import contextmanager
import win32com.client
from pythoncom import com_error

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

@contextmanager
def open_excel():
    excel = win32com.client.Dispatch("Excel.Application")
    excel.Visible = False
    try:
        yield excel
    finally:
        excel.Quit()

@contextmanager
def open_workbook(excel, filename):
    doc = excel.Workbooks.Open(filename)
    try:
        yield doc
    finally:
        doc.Close(True)

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

with excel_cm() as excel:
    for file in all_files_test:    
        try: 
            with open_workbook(file) as doc:
                doc.SaveAs(Filename=file[:-4] + 'csv', FileFormat=6)
        except com_error as e:
            print(file)
            traceback.print_exc()