EXCEL application window not closing on workbook.close()

Question

When I open a workbook using

wbTest = xlwings.Book('test.xlsm')

the EXCEL application opens and shows the workbook. But when doing a

wbTest.close()

afterwards, the workbook closes, but the EXCEL window stays open so that I have to close it manually, even though xlwings.apps returns an empty list:

Is there a way to close the EXCEL window as soon as the last workbook closes?

In the official documentation (readthedocs) I could not find anything solving this question, so far.

Answer 1

Sometimes, a loaded workbook contains macros which leave the application in an "unsaved" state, even when no changes had been made, yet. Using wbTest.app.quit(), in this case, will prompt a save dialog, which I don't want to see. Using wbTest.app.kill() would close the workbook (and close the EXCEL window), but on re-opening EXCEL, a recovery dialog for the killed wbTest workbook will be displayed, which I'd like to avoid.

So, here the overall solution which works for me:

import xlwings
import tempfile
import os

# ... some code creating at least one workbook "wbTest"

# check, if there is only one workbook left and we don't want to save it
if len(xlwings.apps) == 1:

    #save the remaining workbook into temporary folder
    wbTest.save(
        os.path.join(
            tempfile.gettempdir(),
            'test.xlsm',
        )
    )

    # close the application
    wbTest.app.quit()

Thanks for all the hints.

Answer 2

On Windows, xlwings currently requires a workbook in order to communicate with Excel. But you can achieve want you want by quitting the app rather than just closing the workbook (you could check first if there are more than one workbooks open in that app via len(wbTest.app.books)):

wbTest.app.quit()

You may want to save the workbook first or alternatively there's also app.kill().

Answer 3

Along with the proposed solution given by the Author, I figured out that sometimes when you have macros on the Personal Macro Workbook, the counter of opened workbooks changes. That's why I added a simple function that handles that situation.

In my case sometimes I work with multiple excel files opened and this is very handy to get rid of the workbooks that are affected by the macros but you don't want to be closed.

def quit_excel(wb):
    """wb: workbook object from xlwings"""

    print(wb.app.books)  # for debugging and visualization of opened workbooks

    # look if PERSONAL.XLSB is in the list of books associated with the Excel App
    if "PERSONAL.XLSB" in [b.name for b in wb.app.books]:

        if len(wb.app.books) == 2: 
            print("personal, 2, quitting")
            wb.app.quit()

        else:
            print("personal, closing")
            wb.close()

    else:


        if len(wb.app.books) == 1: 
            print("no personal, 1, quitting")
            wb.app.quit()

        else:
            print("no personal, closing")
            wb.close()

Answer 4

I wanted share a solution that may help some users with a slight variation to the original problem. In some scenarios I had multiple excel workbooks open and before running the code therefore I only wanted to close the workbook that the script opened and not the entire excel application. Here's my solution that will work for this scenario.

wb = xw.Book(file_path)
excel_app = xw.apps.active

#
# do stuff with workbook
#

# close workbook if more then one workbook is open. 
# you won't get empty grey excel app since you have another workbook open.
if xw.apps.count > 1:
     wb.close()
# close excel application if only one workbook is open
else:
     excel_app.quit()