Error while working with excel using python

Question

while my script is updating one excel same time if i am going to do any other work manually with another excel error occurs i am using dispatch

     from win32com.client import Dispatch

     excel    = Dispatch('Excel.Application')
excel.Visible   = True 

file_name="file_name.xls"
workbook = excel.Workbooks.Open(file_name)
workBook  = excel.ActiveWorkbook
sheet=workbook.Sheets(sheetno)

I am geting error like this (, com_error(-2147418111, 'Call was rejected by callee.', None, None)

Is there is any way to overcome it ..can i update another excel without geting error..

Solution 1

I encountered this same issue recently. While it sounds like there can be multiple root causes, my situation was occurring because Python was making subsequent calls too quickly for Excel to keep up, particularly with external query refreshes. I resolved this intermittent "Call was rejected by callee" error by inserting time.sleep() between most of my calls and increasing the sleep argument for any calls that are particularly lengthy (usually between 7-15 seconds). This allows Excel the time to complete each command before Python issued additional commands.

Solution 2

This error occurs because the COM object you're calling will reject an external call if it's already handling another operation. There is no asynchronous handling of calls and the behavior can seem random.

Depending on the operation you'll see either pythoncom.com_error or pywintypes.com_error. A simple (if inelegant) way to work around this is to wrap your calls into the COM object with try-except and, if you get one of these access errors, retry your call.

For some background see the "Error Handling" section of the chapter 12 excerpt from Python Programming on Win32 by Mark Hammond & Andy Robinson (O'Reilly 2000).

There's also some useful info specifically about Excel in Siew Kam Onn's blog post "Python programming with Excel, how to overcome COM_error from the makepy generated python file".

Solution 3

I have been struggling with the same problem, but now I have made a solution that works for me so far.

I created a class, ComWrapper, that I wrap the Excel COM object in. It automatically wraps every nested object and call in ComWrapper, and unwraps them when they are used as arguments to function calls or assignments to wrapped objects. The wrapper works by catching the "Call was rejected by callee"-exceptions and retrying the call until the timeout defined at the top is reached. If the timeout is reached, the exception is finally thrown outside the wrapper object.

Function calls to wrapped objects are automatically wrapped by a function _com_call_wrapper, which is where the magic happens.

To make it work, just wrap the com object from Dispatch using ComWrapper and then use it as usual, like at the bottom of the code. Comment if there are problems.

import win32com.client
from pywintypes import com_error
import time
import logging

_DELAY = 0.05  # seconds
_TIMEOUT = 60.0  # seconds


def _com_call_wrapper(f, *args, **kwargs):
    """
    COMWrapper support function. 
    Repeats calls when 'Call was rejected by callee.' exception occurs.
    """
    # Unwrap inputs
    args = [arg._wrapped_object if isinstance(arg, ComWrapper) else arg for arg in args]
    kwargs = dict([(key, value._wrapped_object)
                   if isinstance(value, ComWrapper)
                   else (key, value)
                   for key, value in dict(kwargs).items()])

    start_time = None
    while True:
        try:
            result = f(*args, **kwargs)
        except com_error as e:
            if e.strerror == 'Call was rejected by callee.':
                if start_time is None:
                    start_time = time.time()
                    logging.warning('Call was rejected by callee.')

                elif time.time() - start_time >= _TIMEOUT:
                    raise

                time.sleep(_DELAY)
                continue

            raise

        break

    if isinstance(result, win32com.client.CDispatch) or callable(result):
        return ComWrapper(result)
    return result


class ComWrapper(object):
    """
    Class to wrap COM objects to repeat calls when 'Call was rejected by callee.' exception occurs.
    """

    def __init__(self, wrapped_object):
        assert isinstance(wrapped_object, win32com.client.CDispatch) or callable(wrapped_object)
        self.__dict__['_wrapped_object'] = wrapped_object

    def __getattr__(self, item):
        return _com_call_wrapper(self._wrapped_object.__getattr__, item)

    def __getitem__(self, item):
        return _com_call_wrapper(self._wrapped_object.__getitem__, item)

    def __setattr__(self, key, value):
        _com_call_wrapper(self._wrapped_object.__setattr__, key, value)

    def __setitem__(self, key, value):
        _com_call_wrapper(self._wrapped_object.__setitem__, key, value)

    def __call__(self, *args, **kwargs):
        return _com_call_wrapper(self._wrapped_object.__call__, *args, **kwargs)

    def __repr__(self):
        return 'ComWrapper<{}>'.format(repr(self._wrapped_object))


_xl = win32com.client.dynamic.Dispatch('Excel.Application')
xl = ComWrapper(_xl)

# Do stuff with xl instead of _xl, and calls will be attempted until the timeout is
# reached if "Call was rejected by callee."-exceptions are thrown.

I gave the same answer to a newer question here: https://stackoverflow.com/a/55892457/2828033