2

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
4
  • You have to define your own context manager - try to search "python context manager" on google. Commented Jan 5, 2023 at 16:57
  • Does this answer your question? Why does my contextmanager-function not work like my contextmanager class in python? Commented Jan 5, 2023 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 Commented Jan 6, 2023 at 7: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? / stackoverflow.com/questions/45100271/… / Commented Jan 6, 2023 at 8:07

1 Answer 1

0

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
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.