xlwings open workbook using context manager and overwrite it after making changes

Question

What I want to do is to open csv file, make changes inside and save it. My code looks like this:

savePath = r'D:\file.csv'
with xw.App(visible=True) as app:
    wb = app.books.open(savePath)
    sheet1 = wb.sheets[0]
    saveDF = sheet1.range(wb.sheets[0].used_range.address).options(pd.DataFrame, chunksize=30_000, index=False).value
    wb.sheets[0].clear()
    wb.sheets[0].range('A1').options(index=False, header=True).value = saveDF
    wb.api.SaveAs(savePath, FileFormat=FileFormat.xlCSV)

This code kinda works but when it saves the file it asks me if i want to overwrite the file since it already exists.

So what I did was to save it as "csv.txt" file, then remove the ".csv" file and rename ".csv.txt" file back to ".csv" file using code below:

savePath = r'D:\file.csv'
with xw.App(visible=True) as app:
    wb = app.books.open(savePath)
    sheet1 = wb.sheets[0]
    saveDF = sheet1.range(wb.sheets[0].used_range.address).options(pd.DataFrame, chunksize=30_000, index=False).value
    wb.sheets[0].clear()
    wb.sheets[0].range('A1').options(index=False, header=True).value = saveDF
    wb.api.SaveAs(savePath + '.txt', FileFormat=FileFormat.xlCSV)

os.remove(savePath)
os.rename(savePath + '.txt', savePath)

Issue I have here is that I get error:

"PermissionError: [WinError 32] The process cannot access the file because it is being used by another process:"

Which means that Python tries to rename files while its being saved.

So my questions are:

1. Is there a way to overwrite csv file without needing to manually click "file already exists - do You want ot ovewrite it" prompt ?

2. Is there anything I can change in my code to not get [WinError 32] shown above ?

3. How can I change my code to not open two instances of excel (now it opens blank one - probably when I use "with" statement and second one with my file when I use app.books.open) ?

Thank You in advance for any kind of help.

Answer 1

I had the same problem caused by corrputed excel file saved by Excel Writer.

1. If you are writing an excel file in previous lines with pd.ExcelWriter and later you use the same file for xlwings, use context manager for Writer.
2. You don't have to overwrite the file by xlwings, you can simple do: wb.save(path=None). It will save the same file under the same name. Just be attentive on the file you re using if it is a healty excel file - I mean not corrupted.