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:
Answer from aneroid on Stack Overflow>>> from xlwings import Workbook >>> wb = Workbook() >>> wb.save() >>> wb.save(r'C:\path\to\new_file_name.xlsx')
How to get rid of open excel windows on xlwings?
XLwings save fail
Frequent 'xlwings' Questions - Stack Overflow
python - Xlwings won't close book after saving - Stack Overflow
So I have done the following:
import xlwings as xw app = xw.App() wb = xw.Book(filepath) #Changes to book made here. wb.save() app.quit() #also tried wb.close() and app.kill()
It still leaves all of the excel windows open without the workbooks inside. Is there any way to close all of the empty excel windows?
I have created some dataframes that I want to save to different sheets in an excel workbook. This is a process I will run daily and I want the file name to reflect the time the workbook was generated/saved.
I discovered XLwings and it seems to work really well for what I want to do except for one big problem. I can't get the workbooks to save.
I am generating a workbook name and path using the following code.
from datetime import datetime
import xlwings as xw
#Set the name of the dashboard we will create
now = datetime.now()
dt_string = now.strftime("%d_%m_%Y")
file_name = str("BBGF_Dash_"+ dt_string + ".xlsx'")
#Set the path
path = "'C:/Users/myname/OneDrive/Documents/BBGF/Dashboard/"
#Create the path and name
full = str(path + file_name)
print(full)The output is
'C:/Users/myname/OneDrive/Documents/BBGF/Dashboard/BBGF_Dash_08_10_2020.xlsx'
When I try to plug the variable 'full' which contains the path and filename into the save() function it fails.
wb = xw.Book() wb.save(full)
Here is the error code
~\anaconda3\lib\site-packages\win32com\client\dynamic.py in SaveAs**(self, Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local, WorkIdentity)** com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'The file could not be accessed. Try one of the following:\n\n• Make sure the specified folder exists. \n• Make sure the folder that contains the file is not read-only.\n• Make sure the filename and folder path do not contain any of the following characters: < > ? [ ] : | or *\n• Make sure the filename and folder path do not contain more than 218 characters.', 'xlmain11.chm', 0, -2146827284), None)
What is weird is that if I take the output from print(full) and just plug it into save() manually, it works just fine.
I did make sure the folder is not read-only. I don't see any characters in my file path or file name that could be causing this.
I may be close to the 218 character limit but if I were over I would expect it to cause the error whether i use full or manually paste the output of full.
Would love to solve this one, it's one of the final steps on completing this project.
Here are the two methods that work for me, the first if you are creating a workbook:
import xlwings as xw
app = xw.apps.add()
wb = xw.Book()
wb.close()
app.quit()
Here is an updated answer for 2024 how I got it to work after the app stopped closing the workbook after being loaded from a specific path:
import xlwings as xw
path = [WorkbookPath]
wb = xw.Book(path)
wb.save(path)
app = wb.app
wb.close()
app.kill()
Note it only works if you invoke app.kill() and not app.quit(). Also different syntax with how the app is invoked directly from the workbook.
Updated
You can use wb.app.quit() when you want to close Excel and the associated workbook. Assume that wb is your workbook. Keep in mind that wb.app.quit() does't work if you used wb.close() before wb.app.quit(). Here's an example:
import xlwings as xw
path = r"test.xlsx"
wb = xw.Book(path)
wb.app.quit()
But also consider to open and close workbooks by using with xw.App() as app (since version 0.24.3), I can only recommend it:
import xlwings as xw
with xw.App() as app:
wb = xw.Book("test.xlsx")
# Do some stuff e.g.
wb.sheets[0]["A1"].value = 12345
wb.save("test.xlsx")
wb.close()
The with statement ensures proper acquisition and release of resources. The with statement prevents, when an error occurs before closing Excel properly, the problem that Excel stays open and has possibly hidden excel processes left over in the background (because of xw.App(visible=False), if it is used). The with statement has also the advantage that you don't need app.quit() anymore, because Excel closes anyway at the end of the with block. But wb.close() at the end of the with block is usable (but not necessary) – it achieves that the next time you open Excel, Excel will not display the message that Excel has recovered data that you may want to keep (as explained here).
As a side note, I had a situation where app.quit() did not work. In this case I used app.kill() instead.