Author Topic: pyxll.async_call fails if the function completes and Excel is doing an UI operat  (Read 583 times)

phrrngtn

  • Newbie
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
The async_call will fail in cases such as the user is editing a formula in a cell. There is a similar mechanism in ExcelDNA but that works reliably as it implements a backoff and re-try mechanism by specifically testing for various COM error conditions (see snippet below)

It would be nice if pyxll implemented something similar to make it easier to write applications that run something in the background that will update the workbook (assuming it and Excel are still open when the command completes!)


https://github.com/Excel-DNA/ExcelDna/blob/master/Source/ExcelDna.Integration/ExcelSynchronizationContext.cs

        #region Checks for known COM errors
        const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
        const uint RPC_E_CALL_REJECTED = 0x80010001; // Not sure when we get this one?
                                                     // Maybe when trying to get the Application object from another thread,
                                                     // triggered by a ribbon handler, while Excel is editing a cell.
        const uint VBA_E_IGNORE = 0x800AC472;        // Excel has suspended the object browser
        const uint NAME_NOT_FOUND = 0x800A03EC;      // When called from the main thread, but Excel is busy.

        static bool IsRetry(COMException e)
        {
            uint errorCode = (uint)e.ErrorCode;
            switch (errorCode)
            {
                case RPC_E_SERVERCALL_RETRYLATER:
                case VBA_E_IGNORE:
                case NAME_NOT_FOUND:
                case RPC_E_CALL_REJECTED:
                    return true;
                default:
                    return false;
            }
        }
        #endregion

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 594
  • Karma: +14/-1
    • View Profile
Hi,

yes, I agree. I've actually been thinking of rewriting the async_call function so that it uses a WM_TIMER message to call the function in the main thread, instead of using a background thread. Marshaling Excel COM objects across thread boundaries seems to cause more problems than it's worth.

thanks for the feedback.
Tony

phrrngtn

  • Newbie
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
I don't think you need to resort to that. I would be happy with a mechanism that forbade any COM refs in *args or **kwargs and ran the callable in any old thread. I appended the mechanism I use in ExcelDNA that is used from a menu-item that starts off a long-running computation remotely and asynchronously but with a completion block that enqueues the result to be written back to Excel (via the COM interface). This is very reliable but I would like to be able to do the same thing in Python but without communicating between the addins (which sound very hard).

I will have a go at a pure-python implementation of what I want that uses get_active_object and re-implements the COM exception-processing logic from ExcelDNA's ExcelAsyncUtil.QueueAsMacro mechanism. I presume that get_active_object will succeed irrespective of whether Excel is busy or not and that it will only be when we actually attempt to call something on the xl_app() object that we will get errors if Excel is busy (e.g. with editing a cell)

pjjH





         ExcelReference _destination = (ExcelReference)destination;
                        Task<string>.Factory.StartNew(() => {
                     // We need to use WebClientWithTimeout as the default timeout in 120 seconds (2 minutes)
                     // which is not long enough to run some of our gnarlier functions.
                     WebClientWithTimeout req = new WebClientWithTimeout();
                     return encoding.GetString(req.UploadData(uri_object, "POST", post_data));
                     }).ContinueWith(t =>
                           ExcelAsyncUtil.QueueAsMacro(() => {
                                                                         // XXX: hardwiring the type assumption as Excel complains
                                                                         // about 'number as string' and I don't know how to fix it.
                                                                         // we need to fix this to deal with multiple values and a multi-cell destination range.
                            _destination.SetValue(float.Parse(t.Result));
                                                                         }
                                                                                       )
               );

      }