c# Excel Interop alternatives to waiting to handle 0x800AC472 error

Question

I have an application that writes many times to a formula/macro-laden workbook. It loops through some data 3 times creating, filling, saving, then closing an excel file in each iteration. While it works fine when it's the only version of itself running, if there are multiple instances of it running it has trouble.

Specifically, I'm getting a 0x800AC472 error when writing data to certain cells. It isn't the same cell or value each time but it has seemed to be on the second pass through each time. This is the relevant code:

public void SetCellValue(int row, int col, string val)
{
    if (_currWorkSheet != null)
    {
        string parms = string.Format("row={0}; col={1}; val={2}", row.ToString(), col.ToString(), val);
        for (short i = 0; i < _maxRetries; i++)
        {
            try { (_currWorkSheet.Cells[row, col] as Range).Value2 = val; return; }
            catch (Exception ex) { HandleError(ex, parms); }
        }
        Exception newExc = new Exception("Too many retries attempting to set cell value.");
        newExc.Data.Add("parms", parms);
        throw newExc;
    }
}


    private void HandleError(Exception exc, string parms)
    {
        if (exc != null && exc.Message != null)
        {
            // Excel error that just needs more time to complete. http://social.msdn.microsoft.com/forums/en-US/vsto/thread/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/
            if (exc.Message.Contains("0x800AC472"))
                Thread.Sleep(_threadSleepMs); // Give excel a chance to catch up, then keep processing.
            else
            {
                Exception newExc = new Exception("Unexpected Error", exc);
                newExc.Data.Add("parms", parms);
                throw newExc;
            }
        }
    }

I've set the _maxRetries to 10 and the _threadSleepMs to 500 and continue to get the error, so I don't think that increasing it anymore makes sense.

I was wondering if there are alternatives to sleeping the thread to give it a chance to get "unstuck" as it were.

And maybe this would qualify as a second question but I'm not as concerned about this but, when it crashes I still perform a Close() on it in the finally block, but I still have instances of it hanging around. This is how I close it:

public void Dispose()
{
        if (!_disposed)
        {
            if (_currWorkBook != null)
                for (short i = 0; i < _maxRetries; i++)
                {
                    try { _currWorkBook.Close(false, _missing, _missing); break; }
                    catch (Exception ex) { HandleError(ex, ""); }
                }

            if (_app != null)
            {
                if (_app.Workbooks != null)
                    for (short i = 0; i < _maxRetries; i++)
                    {
                        try { _app.Workbooks.Close(); break; }
                        catch (Exception ex) { HandleError(ex, ""); }
                    }

                for (short i = 0; i < _maxRetries; i++)
                {
                    try { _app.Quit(); break; }
                    catch (Exception ex) { HandleError(ex, ""); }
                }

                if (_currWorkSheet != null)
                {
                    Marshal.ReleaseComObject(_currWorkSheet);
                    _currWorkSheet = null;
                }
                if (_currWorkBook != null)
                {
                    Marshal.ReleaseComObject(_currWorkBook);
                    _currWorkBook = null;
                }
                Marshal.ReleaseComObject(_app);
                _app = null;
            }

            GC.Collect();
            _disposed = true;
        }
}

It doesn't throw and error, so I was just wondering if there were any holes in it?

Thank you, Jeff

Answer 1

In my experience Excel cannot be made to run reliably with multiple instances being driven from COM Interop. We have a lot of test code which we run against Excel so we've tried to get this to work. The only solution is to limit your application to one instance at a time - and even then my experience is that there is an occasional unexplained exception.

SpreadsheetGear for .NET might solve your problem. SpreadsheetGear has a "workbook set" which is roughly analogous to an Excel application, and supports any number of workbook sets being used from different threads.

You can see live ASP.NET samples here and download the free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC

Answer 2

The only solution I was able to come up with was to create a lock on the thread once it needs to use the excel functionality. This just ensured I only have one process using excel at one time. It's not perfect, especially if unrelated processes also try to use excel, but it was the only fix I could come up with.