Excel doesn't close if I use the "with" keyword instead of "inline" python

Question

I use python to refresh some power query table in Excel using win32com.client library.

If I write everything "inline" (1st version): excel closes correctly
If I use a "with" statement to open/close excel and workbooks (2nd version): excel process doesn't close

==> Why is it not doing the same thing?

==> How can I make the "with" version working?

Code "inline" : excel closes correctly:

# Start excel app
xlapp = win32.gencache.EnsureDispatch('Excel.Application')
xlapp.Visible = False
xlapp.Interactive = False
excel_constant = win32.constants

# Open workbook
file_name = str(file)
wb = xlapp.Workbooks.Open(file_name)

#Refresh power query
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()

# Close workbook
wb.Close(SaveChanges=True)
wb = None
gc.collect()

# Quit excel app
xlapp.Quit()
xlapp = None
gc.collect()

Using "with" the excel process stays active

class start_excel(object):
    def __init__(self):
        pass

    def __enter__(self):
        self.xlapp = win32.gencache.EnsureDispatch('Excel.Application')
        self.xlapp.Visible = False
        self.xlapp.Interactive = False

        return self.xlapp

    def __exit__(self, *args):
        self.xlapp.Quit()
        self.xlapp = None
        gc.collect()


class open_workbook(object):
    def __init__(self, xlapp, file_name):
        self.file_name = file_name
        self.xlapp = xlapp
     
    def __enter__(self):
        self.chemin_fichier = str(self.file_name)
        self.wb = self.xlapp.Workbooks.Open(self.chemin_fichier)
        
        return self.wb
 
    def __exit__(self, *args):
        self.wb.Close(SaveChanges=True)
        self.wb = None
        gc.collect()



with start_excel() as xlapp:
    # excel_constant = win32.constants
    with open_workbook(xlapp, file) as wb:
        wb.RefreshAll()
        xlapp.CalculateUntilAsyncQueriesDone()

EDIT: 3rd example, with a contextlib.contextmanager (same issue)

import contextlib

@contextlib.contextmanager
def start_excel():
    try:
        xlapp = win32.gencache.EnsureDispatch('Excel.Application')
        xlapp.Visible = False
        xlapp.Interactive = False
        yield xlapp

    finally:
        xlapp.Quit()
        xlapp = None
        gc.collect()

@contextlib.contextmanager
def open_workbook(xlapp, file_name):
    try:
        chemin_fichier = str(file_name)
        wb = xlapp.Workbooks.Open(chemin_fichier)
        yield wb
    
    finally:
        wb.Close(SaveChanges=True)
        wb = None
        gc.collect()



with start_excel() as xlapp:
    # excel_constant = win32.constants
    with open_workbook(xlapp, file) as wb:
        wb.RefreshAll()
        xlapp.CalculateUntilAsyncQueriesDone()

EDIT: code below works but is not satisfying I added the 2 last lines: wb = None & xlapp = None, Excel closes correctly. But it doesn't feel safe, is there a way to ensure that the with statement cleanly clear its variables? I feel like it's easy to forget to include these additionnal lines every time I use with.

class start_excel(object):
    def __init__(self):
        pass

    def __enter__(self):
        self.xlapp = win32.gencache.EnsureDispatch('Excel.Application')
        self.xlapp.Visible = False
        self.xlapp.Interactive = False

        return self.xlapp

    def __exit__(self, *args):
        self.xlapp.Quit()
        self.xlapp = None
        gc.collect()


class open_workbook(object):
    def __init__(self, xlapp, file_name):
        self.file_name = file_name
        self.xlapp = xlapp
     
    def __enter__(self):
        self.chemin_fichier = str(self.file_name)
        self.wb = self.xlapp.Workbooks.Open(self.chemin_fichier)
        
        return self.wb
 
    def __exit__(self, *args):
        self.wb.Close(SaveChanges=True)
        self.wb = None
        gc.collect()



with start_excel() as xlapp:
    # excel_constant = win32.constants
    with open_workbook(xlapp, file) as wb:
        wb.RefreshAll()
        xlapp.CalculateUntilAsyncQueriesDone()
        wb = None
    
    xlapp = None

You have to define your own context manager - try to search "python context manager" on google. — TDG, Jan 05 '23 at 16:57
Does this answer your question? [Why does my contextmanager-function not work like my contextmanager class in python?](https://stackoverflow.com/questions/15447130/why-does-my-contextmanager-function-not-work-like-my-contextmanager-class-in-pyt) — TDG, Jan 05 '23 at 17:10
No, it does not: - I understand that using '''__enter__''' and '''__exit__''' is already the right way to do it - I'll add an example with "@contextlib.contextmanager" but it has the same issue — Nico, Jan 06 '23 at 07:49
I think I found the issue, but i'm not sure what is the best practice: "with" statement does not create a scope, therefore wb and xlapp are not cleanly set to None when the with Statement is closed. / However, it means that I need to add some code outside of the context manager to cleanly close excel. Is there a way to keep it in the contextmanager in order to be sure it closes? / https://stackoverflow.com/questions/45100271/scope-of-variable-within-with-statement / — Nico, Jan 06 '23 at 08:07

score 0 · Accepted Answer · answered Jan 09 '23 at 08:05

I understood the issue:

Excel needs the variable set to None so it can close
Using with keyword, there are 2 references : the reference in the generator and the one in the "main" code. the __exit__ part remove only one of the 2 references.

The last version works as expected, but I don't like that you need to do some mandatory clean up outside of the __exit__ part.

class start_excel(object):
    def __init__(self):
        pass

    def __enter__(self):
        self.xlapp = win32.gencache.EnsureDispatch('Excel.Application')
        self.xlapp.Visible = False
        self.xlapp.Interactive = False

        return self.xlapp

    def __exit__(self, *args):
        self.xlapp.Quit()
        self.xlapp = None
        gc.collect()


class open_workbook(object):
    def __init__(self, xlapp, file_name):
        self.file_name = file_name
        self.xlapp = xlapp
     
    def __enter__(self):
        self.chemin_fichier = str(self.file_name)
        self.wb = self.xlapp.Workbooks.Open(self.chemin_fichier)
        
        return self.wb
 
    def __exit__(self, *args):
        self.wb.Close(SaveChanges=True)
        self.wb = None
        gc.collect()



with start_excel() as xlapp:
    # excel_constant = win32.constants
    with open_workbook(xlapp, file) as wb:
        wb.RefreshAll()
        xlapp.CalculateUntilAsyncQueriesDone()
        wb = None
    
    xlapp = None

Excel doesn't close if I use the "with" keyword instead of "inline" python

1 Answers1

Linked