COM Interop - Wait for Excel to Complete Operation

Question

Hello,

I am doing some COM Interop work with Excel and other Office Automation Software.

A Co-Worker has mentioned to me that I need to wait for these Automation Servers to become ready after issuing a command to them.

I, however, cannot see the purpose of this as don't all calls block until the automation server completes the given task?

For example I would normally write:

Dim o as AutomationObject = AutomationServer.CreateObject(x, y, z)
o.Property ' could throw COM exception!!????

My co-worker says I need to sleep after that call because the the Automation Server could still be working on creating and initializing the object.

Dim o as AutomationObject = AutomationServer.CreateObject(x, y, z)
Threading.Sleep(5000) ' wait for AutomationServer to "become ready"
o.Property ' could still throw COM exception!!????

The problem I have with this is that the AutomationServer calls should block until the AutomationServer finishes or completes what it was working on or at the very least it should be a loop checking if "o" is nothing, but that makes no sense because once the call returns its done!

My question is, Is there any benefit to Sleeping after an AutomationServer call? Is there a method to "wait" until the AutomationServer finishes (if it does not in fact block)?

Answer 1

You can indeed get an exception when accessing an out-proc Automation server that is busy. In the case of Office, one way this can happen is if you have a macro that runs when you open a document. You will also have problems if you connect to an instance of an Office application that has a dialog box open.

This article describes how you can avoid this by implementing IOleMessageFilter error handlers. The article is about automation of Visual Studio, but the principles and techniques are the same for automating other out-proc automation servers such as Office.

Answer 2

A simple way to deal with this is below. I had to interface with Visual Studio recently, and I found this worked well, if you are willing to fail out quickly on the COM object becoming infinitely busy. See if this will work for you:

    delegate void COMWrapper();

    private void MakeRiskyCOMCall(COMWrapper doThis)
    {
        bool sendAgain = true;
        int count = 0;

        while (sendAgain)
        {
            try
            {
                doThis();
                sendAgain = false;
            }
            catch (COMException ex)
            {
                System.Threading.Thread.Sleep(50);
                sendAgain = count > 100 ? false : true;
                if (!sendAgain)
                {
                    throw;
                }
            }
            finally { count++; }
        }
    }

After you have this delegate and method in scope, then here are examples of a method call and a property access using anonymous delegation.

    // property access
    vsDisplay display;
    MakeRiskyCOMCall(delegate() { display = dte.DisplayMode; });
    if (display == null)
        throw new Exception("VS Display not set");        

    //simple method call
    MakeRiskyCOMCall(delegate() { dte.Solution.Close(true); });

I deliberately hid the COMException, since I didn't want it, but if you need it just set a COMException variable within the MakeRiskyCOMCall method that is scoped within the calling method. You could also check for the specific HResult that indicates waiting for a busy process and fail more quickly for other HResults. This solution is quick and avoids all the COM interop messiness, and it's downside is that it isn't pretty, but it works and is easier to figure out for the maintainers of your code (I think).