Python win32com.client and "with" statement

Question

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've provided some useful key details, but what do you mean by "is it okay?" what have you tried? what happened when you tried? in what way were the results unexpected? if they weren't unexpected, what about it is making you wonder whether or not it's "okay"? please elaborate a bit, and I think it will be easier for others to answer your question; HTH — landru27, Nov 17 '18 at 20:07
Thank you @landru27 - I have updated the question with the exception that occurs when I use the with statement, which should help explain why I am asking the question. — smugs, Nov 17 '18 at 20:22

score 4 · Accepted Answer · answered Nov 18 '18 at 09:47

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.

Python win32com.client and "with" statement

1 Answers1