python win32com close Excel process

Question

Trying to alter Excel_sheet by python and totally confused in process recovery.

import win32com.client
class XlsClass:
    def __init__(self ,filename=None ,*,Visible=False ,Alerts=False):
        self.xlApp = win32com.client.Dispatch('Excel.Application')
        self.xlApp.Visible = Visible
        self.xlApp.DisplayAlerts = Alerts
        if filename:
            self.filename = filename
            self.xlBook = self.xlApp.Workbooks.Open(filename)
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
            self.filename = ''

    def __del__(self):
        self.xlBook.Close()
        self.xlApp.Quit()

Sometimes the code works well ,but sometimes python will raise an error just like 'self.xlApp.Visible cant be set?'. This always happened in a loop just like:

for fname in filelist:
    xlbook = XlsClass(fname)
    #do something

Then I have checked my 'windowstasksmanager' and Notice that

xlbook = Dispatch('Excel.Application') 

wil create a process named 'EXCEL.EXE*32'. When I type in 'xlbook.Quit()' the process was still there!? So may be the 'cant be set' error since this residual process? After I call the func 'Dispatch' ,how can I totally close it?

del xlbook

Cant kill the process so how it works?

Bad in english ,waiting for help....thank you.

================================================

2014/3/10: I catch the error again and capture traceback...

Traceback (most recent call last):
  File "C:\work_daily\work_RecPy\__RecLib\XlsClass.py", line 9, in __init__
    self.xlApp.Visible = Visible
  File "C:\Program Files\python33\lib\site-packages\win32com\client\dynamic.py", 
  line 576, in __setattr__
  raise AttributeError("Property '%s.%s' can not be set." % (self._username_,attr))
AttributeError: Property 'Excel.Application.Visible' can not be set.

I tried del self.xlApp or xlbook = None before creating a new XlsClass() but seems not working...

Solution

This is likely due to python's garbage collection: every time through the loop you create a XlsClass; when one iteration is over, the instance of XlsClass is no longer referenced so it is available for garbage collection, but this may not happen immediately. So next time through the loop you dispatch a new Excel but the previous one may not be totally shut down yet. Try forcing a garbage collection before the next iteration:

for fname in filelist:
    xlbook = XlsClass(fname)
    #do something
    xlbook = None
    gc.collect()

Update:

If this doesn't work, you could try attaching to a running instance, i.e. once dispatched, do not close the app, just close the workbook. In that case you would probably want to do something like this:

class XlsClass:
    def __init__(self, xlApp, filename=None ,*,Visible=False ,Alerts=False):
        self.xlApp = xlApp
        self.xlApp.Visible = Visible
        ...

    def otherMethod(self):
        # ....

    def close(self):
        self.xlBook.Close()
        self.xlBook = None
        self.xlApp = None
        # don't do anything with self.xlApp or self

xlApp = win32com.client.Dispatch('Excel.Application')
for fname in filelist:
    xlbook = XlsClass(xlApp, fname)
    # do something with xlbook
    xlbook.close()

Note that it is better to explicitly close the book than to wait for the __del__ to be executed by the garbage collector.

Also, you could use win32com.client.GetObject (filename) which will get existing instance if one exists, automatically create one if not; you could put GetObject in the __init__. In this case you would have to close Excel, if it was opened, before exiting your script, by doing a final GetActiveObject (see Python/win32com - Check if Program is Open) and then a Close. In my win32com scripts I check if Excel is already running when script starts, if so I print an error asking user to close, just in case user had open Excel books easier to let them cleanup and exit so you start from known state.

Comments

[Admin]

I have try this yet but error still happened and "del self.xlApp" not works too