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
You can use
object_name.close
, which returns False if the file is not properly closed.Using your code:
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: