Com_Error when trying to save a .xlsm with Xlwings/ Python

Question

I am trying to separate a single workbook with many sheets into single workbooks, each with one sheet. My code below works until I try to save the new workbook. I have tried raw strings, absolute file paths, and relative file paths. Nothing seems to work. I get the error below. How can I save this file?

#! python 3
import xlwings as xw
import pandas as pd
x= 0

wb= xw.Book('Copy of US Distribution Income Stmt FOR REVIEW FZN DFC.xlsm')

worksheet_names = wb.sheets

for worksheet in wb.sheets:
    for worksheet in wb.sheets:
        if worksheet != wb.sheets[x]:
            worksheet.delete()
    wb.save(r'PRELIM P&L - ' + str(worksheet) + ".xlsm"'')
    x= x+1
---------------------------------------------------------------------------
com_error                                 Traceback (most recent call last)
<ipython-input-9-7b2ca719ce21> in <module>
     13             worksheet.delete()
     14     wb.save()
---> 15     wb.save(r'PRELIM P&L - ' + str(worksheet) + ".xlsm"'')
     16     x= x+1
     17 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\xlwings\main.py in __repr__(self)
    993 
    994     def __repr__(self):
--> 995         return "<Sheet [{1}]{0}>".format(self.name, self.book.name)
    996 
    997     @property

~\AppData\Local\Continuum\anaconda3\lib\site-packages\xlwings\main.py in name(self)
    887     def name(self):
    888         """Gets or sets the name of the Sheet."""
--> 889         return self.impl.name
    890 
    891     @name.setter

~\AppData\Local\Continuum\anaconda3\lib\site-packages\xlwings\_xlwindows.py in name(self)
    609     @property
    610     def name(self):
--> 611         return self.xl.Name
    612 
    613     @name.setter

~\AppData\Local\Continuum\anaconda3\lib\site-packages\xlwings\_xlwindows.py in __getattr__(self, item)
    120         while True:
    121             try:
--> 122                 v = getattr(self._inner, item)
    123                 if isinstance(v, (CDispatch, CoClassBaseClass, DispatchBaseClass)):
    124                     return COMRetryObjectWrapper(v)

~\AppData\Local\Continuum\anaconda3\lib\site-packages\win32com\client\__init__.py in __getattr__(self, attr)
    472                 if args is None:
    473                         raise AttributeError("'%s' object has no attribute '%s'" % (repr(self), attr))
--> 474                 return self._ApplyTypes_(*args)
    475 
    476         def __setattr__(self, attr, value):

~\AppData\Local\Continuum\anaconda3\lib\site-packages\win32com\client\__init__.py in _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args)
    465         def _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args):
    466         return self._get_good_object_(
--> 467                         self._oleobj_.InvokeTypes(dispid, 0, wFlags, retType, argTypes, *args),
    468             user, resultCLSID)
    469 

com_error: (-2146827864, 'OLE error 0x800a01a8', None, None)

Answer 1

Welcome to stackoverflow.
The problem with your code is that worksheet is an sheet object. It doesn't help to do str(worksheet), this gives you the sheet object as string, e.g. "<Sheet[filename.xlsx]Sheet1>", which has characters that are not allowed for filenames.
I think what you want is worksheet.name, which gives you just the sheet name, e.g. "Sheet1". That means the second last line of your code should be:

wb.save("PRELIM P&L - " + worksheet.name + ".xlsm")