Closing Excel using Python

Closing Excel from Python using COM seems pretty simple, but there’s a few little caveats to remember.Normally, closing Excel simply involves calling the Quit() method on the application.

In VBA it’d simply be a

Application.Quit

In python:

xl = win32com.client.Dispatch("Excel.Application")
xl.Quit() #quit excel, as if user hit the close button/clicked file->exit.
# -- OR --
xl.ActiveWorkBook.Close #close the active workbook

This is the normal and expected behavior, it’ll ask the user to save any unsaved workbooks that are being closed, giving the user a chance to finish anything.

But sometimes you don’t want to have the normal behavior.
Alternative 1 – brute force

The VBA documentation suggests that if you really want to force a quit without prompting for save, you can set the boolean property DisplayAlerts to False. This will make it so that Excel won’t display any alerts and close.

We can do the same in Python:

xl.DisplayAlerts = False #force excel to not display alerts
xl.Quit() #then quit the app

As you can imagine, this isn’t really recommended. The user can have more sheets open than what you know about, and a force close is a good way to destroy data and make the user angry.

Also, setting variables that affect the entire application to something other than the normal defaults tends to be a bad idea. Your specific program might exit and not clean up after itself, so your settings will remain, and the user is not aware of the changes… a recipe for disaster.

Alternative 2

The next alternative is that each workbook has a Saved boolean property. This is the flag that’s set to True when the user saves, and is changed to False when something changes.

The interesting part is that this isn’t a read-only variable, you can set it to what you want. Meaning, if you’re manipulating a workbook and really want to close it, you can use something like:

xl.ActiveWorkbook.Saved = True
xl.ActiveWorkbook.Close #just clear the workbook you want without a fuss.

Cleanup

Finally, there’s important cleanup that happens after you close down Excel in Python. You used a COM Dispatch to interact with Excel, the act of using Dispatch meant that Excel’s loaded into memory. If you look at your list of processes, EXCEL.EXE should be in there.

The thing to remember is that even if you call xl.Quit() from python, the xl variable still exists, and is holding onto that connection to the Excel process. If you decide to send the command xl.Visible=True, Excel will pop back up again!

Sometimes, for debugging purposes, or just because Excel is being Excel and broke in some weird way, it’s important to fully close Excel, process and all, before restarting.

So the thing to remember is to clean up and destroy the link when you don’t need it. You can use the del python keyword to do this. Normally, if the Py app you’re using is closed, it’ll clean up after itself, so you don’t have to think too much about doing it explicitly.

However, if you’re making multiple Dispatch objects and connecting them to Excel for some reason, the Excel process won’t fully quit until the last one is cleaned up.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>