COM client and exceptions

[Duncan Booth]

"Blair Hall" <b.hall at irl.cri.nz> wrote in 
news:01c226c5$ac44d480$d708cb83 at hallb.irl.cri.nz:

> For example, using the class defined below, I can write
> 
>>>> xl = Excel('test')
> 
> and then to provoke the problem type 
> 
>>>> xl.problem()
> 
> After which Excel does not operate properly. So for example, typing
> 
>>>> xl = Excel("test2")
> 
> does not open Excel properly: only part of the Excel toolbar on screen.
> 
> I  am using win95 with the latest version of Python and the windows
> extensions.
> 
> What should I be doing to keep Excel happy? 
> Indeed, where should I be looking (documentation?)
> to find out how to write more stable Com-Python code?

I haven't tested any of this out with live code, so I could be wrong, but 
here is what I think is happening:

First you create your object and assign it to xl. xl.app holds the COM 
pointer. When you call problem, a traceback object is created which 
includes a reference to the xl object. This means that when you overwrite 
xl with another object, there is still a reference to the original which 
will not be released (i.e. __del__ is not called) at least until another 
exception is raised. Even without the exception, by the time __del__ is 
called, ActiveWorkbook probably refers to the wrong workbook.

The most obvious thing to try doing is to stop depending on the implicit 
call to __del__. I would rewrite your class to have an explicit close 
method and make sure you call that explicitly whenever you think you have 
finished with the object. The other thing you might consider would be to 
hold onto the wb reference so that you can support overlapping lifetimes.

class Excel:
    def __init__(self,filename,visible=1):
        ...
        self.wb = wb
        ...
    def __del__(self):
        self.close()

    def close(self):
        if sel.app:
            try:
                self.wb.Save()
                del self.wb
                self.app.Quit()
            finally:
                self.app = None

And then try:
xl = Excel('test')
try:
    xl.problem()
finally:
    xl.close()
xl = Excel("test2")

-- 
Duncan Booth                                             duncan at rcp.co.uk
int month(char *p){return(124864/((p[0]+p[1]-p[2]&0x1f)+1)%12)["\5\x8\3"
"\6\7\xb\1\x9\xa\2\0\4"];} // Who said my code was obscure?