Python win32com.client and "with" statement

Good afternoon,

I am writing some ETL scripts with Python, and am currently using win32com.client to open and refresh some data connections in Excel.

My question is this: should I be using a with statement to open/close "Excel.Application" as such

import win32com.client
xl = win32com.client.DispatchEx("Excel.Application")

def wb_ref(file):
    xl.DisplayAlerts = False
    with xl.workbooks.open(file) as wb:
        wb.RefreshAll()
        wb.Save()

wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')

When I have tried this an exception occurs, so I am obviously not using it correctly.

Traceback (most recent call last):
  File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 32, in <module>
    wb_ref( 'C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')
  File "C:/Users/smugs/Documents/Python Scripts/Work/km_jobs/utils/xl_conv.py", line 11, in wb_ref
    with xl.workbooks.open(file) as wb:
AttributeError: __enter__

Or do I need to explicitly call the close command

def wb_ref(file):
    xl.DisplayAlerts = False
    wb = xl.workbooks.open(file)
    wb.RefreshAll()
    wb.Save()
    wb.Close()

wb_ref('C:/Users/smugs/Documents/folder_a/workbooks/test.xlsx')

The second example is what I have been using, and it works. I guess I am just wondering what is the more pythonic way to script the above function.

(fyi - first time asker, longtime reader)

You get the AttributeError: __enter__ error because xl.workbooks.open is not a context manager, and so it doesn't support the with statement.

If you want to use a with statement in your code you can use the closing function from the contextlib module in the standard library, like this:

from contextlib import closing

def wb_ref(file):
    xl.DisplayAlerts = False
    with closing(xl.workbooks.open(file)) as wb:
        wb.RefreshAll()
        wb.Save()

contextlib.closing will automatically call close on the object that is passed to it when the code in the with block has completed execution.