How to properly wait to check if an Excel instance has closed after attempting to?

1.8k views Asked by At

I'm using the Python standard library modules and the pythoncom and win32com.client modules from the PyWin32 package to interact with Microsoft Excel.

I get a list of the running Excel instances as COM object references and then when I want to close the Excel instances I first iterate through the workbooks and close them. Then I execute the Quit method and after I attempt to terminate the Excel process if it's not terminated.

I do the check (_is_process_running) because the Excel instance might not close successfully if for example the Excel process is a zombie process (information on how one can be created) or if the VBA listens to the before close event and cancels it.

My current quirky solution to know when to check if it closed is to use the sleep function. It does seem to work but it can fail in certain circumstances, such as if it takes longer than the sleep function waits for.

I thought that clearing all the COM references and collecting the garbage would be enough for the Excel process to terminate if the Quit method does succeed but it still takes some time asynchronously.

The check is in the close method of the _excel_application_wrapper class in the excel.pyw file.


Simple code to generate an Excel zombie process (you can see the process in the task manager):

from os import getpid, kill
from win32com.client import DispatchEx

_ = DispatchEx('Excel.Application')
kill(getpid(), 9)

This is only for testing purposes to help reproduce an Excel instance that won't be closed when calling Quit.

Another way to make Quit fail to close is to add this VBA code to the workbook in Excel:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Cancel = True
End Sub

Code on the excel_test.py file:

import excel
from traceback import print_exc as print_exception

try:
  excel_application_instances = excel.get_application_instances()
  for excel_application_instance in excel_application_instances:
    # use excel_application_instance here before closing it
    # ...
    excel_application_instance.close()
except Exception:
  print('An exception has occurred. Details of the exception:')
  print_exception()
finally:
  input('Execution finished.')

Code on the excel.pyw file:

from ctypes import byref as by_reference, c_ulong as unsigned_long, windll as windows_dll
from gc import collect as collect_garbage
from pythoncom import CreateBindCtx as create_bind_context, GetRunningObjectTable as get_running_object_table, \
  IID_IDispatch as dispatch_interface_iid, _GetInterfaceCount as get_interface_count
from win32com.client import Dispatch as dispatch

class _object_wrapper_base_class():
  def __init__(self, object_to_be_wrapped):
    # self.__dict__['_wrapped_object'] instead of
    # self._wrapped_object to prevent recursive calling of __setattr__
    # https://stackoverflow.com/a/12999019
    self.__dict__['_wrapped_object'] = object_to_be_wrapped
  def __getattr__(self, name):
    return getattr(self._wrapped_object, name)
  def __setattr__(self, name, value):
    setattr(self._wrapped_object, name, value)

class _excel_workbook_wrapper(_object_wrapper_base_class):
  # __setattr__ takes precedence over properties with setters
  # https://stackoverflow.com/a/15751159
  def __setattr__(self, name, value):
    # raises AttributeError if the attribute doesn't exist
    getattr(self, name)
    if name in vars(_excel_workbook_wrapper):
      attribute = vars(_excel_workbook_wrapper)[name]
      # checks if the attribute is a property with a setter
      if isinstance(attribute, property) and attribute.fset is not None:
        attribute.fset(self, value)
        return
    setattr(self._wrapped_object, name, value)
  @property
  def saved(self):
    return self.Saved
  @saved.setter
  def saved(self, value):
    self.Saved = value
  def close(self):
    self.Close()

class _excel_workbooks_wrapper(_object_wrapper_base_class):
  def __getitem__(self, key):
    return _excel_workbook_wrapper(self._wrapped_object[key])

class _excel_application_wrapper(_object_wrapper_base_class):
  @property
  def workbooks(self):
    return _excel_workbooks_wrapper(self.Workbooks)
  def _get_process(self):
    window_handle = self.hWnd
    process_identifier = unsigned_long()
    windows_dll.user32.GetWindowThreadProcessId(window_handle, by_reference(process_identifier))
    return process_identifier.value
  def _is_process_running(self, process_identifier):
    SYNCHRONIZE = 0x00100000
    process_handle = windows_dll.kernel32.OpenProcess(SYNCHRONIZE, False, process_identifier)
    returned_value = windows_dll.kernel32.WaitForSingleObject(process_handle, 0)
    windows_dll.kernel32.CloseHandle(process_handle)
    WAIT_TIMEOUT = 0x00000102
    return returned_value == WAIT_TIMEOUT
  def _terminate_process(self, process_identifier):
    PROCESS_TERMINATE = 0x0001
    process_handle = windows_dll.kernel32.OpenProcess(PROCESS_TERMINATE, False, process_identifier)
    process_terminated = windows_dll.kernel32.TerminateProcess(process_handle, 0)
    windows_dll.kernel32.CloseHandle(process_handle)
    return process_terminated != 0
  def close(self):
    for workbook in self.workbooks:
      workbook.saved = True
      workbook.close()
      del workbook
    process_identifier = self._get_process()
    self.Quit()
    del self._wrapped_object
    # 0 COM references
    print(f'{get_interface_count()} COM references.')
    collect_garbage()
    # quirky solution to wait for the Excel process to
    # terminate if it did closed successfully from self.Quit()
    windows_dll.kernel32.Sleep(1000)
    # check if the Excel instance closed successfully
    # it may not close for example if the Excel process is a zombie process
    # or if the VBA listens to the before close event and cancels it
    if self._is_process_running(process_identifier=process_identifier):
      print('Excel instance failed to close.')
      # if the process is still running then attempt to terminate it
      if self._terminate_process(process_identifier=process_identifier):
        print('The process of the Excel instance was successfully terminated.')
      else:
        print('The process of the Excel instance failed to be terminated.')
    else:
      print('Excel instance closed successfully.')

def get_application_instances():
  running_object_table = get_running_object_table()
  bind_context = create_bind_context()
  excel_application_class_clsid = '{00024500-0000-0000-C000-000000000046}'
  excel_application_clsid = '{000208D5-0000-0000-C000-000000000046}'
  excel_application_instances = []
  for moniker in running_object_table:
    display_name = moniker.GetDisplayName(bind_context, None)
    if excel_application_class_clsid not in display_name:
      continue
    unknown_com_interface = running_object_table.GetObject(moniker)
    dispatch_interface = unknown_com_interface.QueryInterface(dispatch_interface_iid)
    dispatch_clsid = str(dispatch_interface.GetTypeInfo().GetTypeAttr().iid)
    if dispatch_clsid != excel_application_clsid:
      continue
    excel_application_instance_com_object = dispatch(dispatch=dispatch_interface)
    excel_application_instance = _excel_application_wrapper(excel_application_instance_com_object)
    excel_application_instances.append(excel_application_instance)
  return excel_application_instances

This answer suggests checking if the remote procedural call (RPC) server is unavailable by calling something from the COM object. I have tried trial and error in different ways without success. Such as adding the code below after self.Quit().

from pythoncom import com_error, CoUninitialize as co_uninitialize
from traceback import print_exc as print_exception

co_uninitialize()
try:
  print(self._wrapped_object)
except com_error as exception:
  if exception.hresult == -2147023174: # "The RPC server is unavailable."
    print_exception()
  else:
    raise
2

There are 2 answers

1
Kevin Freeman On

You can use object_name.close, which returns False if the file is not properly closed.

Using your code:

def close(self):
  for workbook in self.workbooks:
    workbook.saved = True
    workbook.close()
    if workbook.closed:
        del workbook
    else:
        print("Lookout, it's a zombie! Workbook was not deleted")

However, I should also mention that Pep 343 has an even better solution using Python's with context manager. This will ensure the file is closed before further execution.

Example:

with open("file_name", "w") as openfile:
    # do some work

# "file_name" is now closed
0
sancho.s ReinstateMonicaCellio On

It seems to me that the you know how to detect the current state of Excel instances. The only point you are missing is detecting an event for the Quitting action.

AFAIK, there is no way to detect an event as you mean to. But a (possibly very good) workaround is setting time points, e.g. in a list, and check the status at those points. If you are concerned about wasting 1000ms, and at the same time performing an excessive number of checks, you could set your list as [1, 3, 10, 30, ...], i.e., equispaced in log(time).

Even if there is an event available, I guess your code would be more "elegant", but you won't get any better performance than with the proposal above (unless the wait time is in the range of, say, minutes or above).