I am trying to find out how to save and close to an existing workbook using xlwings after writing in it:
import xlwings as xw
list_of_values = [1, 2, 3]
workbook_path = 'abc.xlsx'
wb = xw.Book(workbook_path)
ws = wb.sheets['sheet1']
ws.range('E35').value = list_of_values
wb.save()
wb.close()
When I get to wb.save(workbook_path), there is a prompt stating:
'A file named abc.xlsx' already exists in this location.
Do you want to replace it?'
I want to overwrite the file immediately without the prompt coming up. According to the docs, wb.save() should automatically overwrite (see: https://docs.xlwings.org/en/v0.6.4/api.html). I have also tried wb.save(workbook_path) but the pop-up still appears.
Appreciate any help on this please.
p.s. - I am basically trying to write data into a pre-formatted excel sheet. If there are other ways that can preserve the formatting, I would be happy to try it. I have tried this but it throws an error at if newCell: Easily write formatted Excel from Python: Start with Excel formatted, use it in Python, and regenerate Excel from Python
Answer
You may need to specify the full path when you specify the path to wb.save():
path(str, default None) – Full path to the workbook.
It will save the file and overwrite without prompting. From their docs:
>>> from xlwings import Workbook >>> wb = Workbook() >>> wb.save() >>> wb.save(r'C:\path\to\new_file_name.xlsx')