writing data from C# to Excel interrupted by opening Excel Window

Question

While my C# program writes data continuously to an Excel spreadsheet, if the end user clicks on the upper right menu and opens the Excel Options window, this causes a System.Runtime.InteropServices.COMException with HRESULT: 0x800AC472 which interrupts the data from being written to the spreadsheet.

Ideally, the user should be allowed to do this without causing an exception.

The only solution I found to this error code was to loop and wait until the exception went away: Exception from HRESULT: 0x800AC472 which effectively hangs the app, data is not written to Excel and the user is left in the dark about the problem.

I thought about disabling the main menu of Excel while writing to it, but cannot find a reference on how to do this.

My app supports Excel 2000 to 2013.

Here is how to reproduce the issue:

Using Visual Studio Express 2013 for Windows Desktop, .NET 4.5.1 on Windows 7 64-bit with Excel 2007.

Create a new Visual C# Console Application project.

Add reference to "Microsoft ExceL 12.0 Object Library" (for Excel) and to "System.Windows.Forms" (for messagebox).

Here is the complete code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading.Tasks;
using System.Threading; // for sleep
using System.IO;
using System.Runtime.InteropServices;
using System.Reflection;
using Microsoft.Win32;
using Excel = Microsoft.Office.Interop.Excel; 

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            int i = 3; // there is a split pane at row two
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;

            try 
            { 
                object misValue = System.Reflection.Missing.Value;

                xlApp = new Excel.Application();
                xlApp.Visible = false;
                xlWorkBook = xlApp.Workbooks.Add(misValue);

                xlApp.Visible = true;
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                // next 2 lines for split pane in Excel:
                xlWorkSheet.Application.ActiveWindow.SplitRow = 2; 
                xlWorkSheet.Application.ActiveWindow.FreezePanes = true;
                xlWorkSheet.Cells[1, 1] = "Now open the";
                xlWorkSheet.Cells[2, 1] = "Excel Options window";
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                System.Windows.Forms.MessageBox.Show("Microsoft Excel does not seem to be installed on this computer any longer (although there are still registry entries for it). Please save to a .tem file. (1)");
                  return;
            }
            catch (Exception)
            {
                System.Windows.Forms.MessageBox.Show("Microsoft Excel does not seem to be installed on this computer any longer (although there are still registry entries for it). Please save to a .tem file. (2)");
                return;
            }

            while(i < 65000)
            {
                i++;

                try
                {
                    xlWorkSheet.Cells[i, 1] = i.ToString();
                    Thread.Sleep(1000);
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    System.Windows.Forms.MessageBox.Show("All right, what do I do here?");
                }
                catch (Exception) 
                {
                    System.Windows.Forms.MessageBox.Show("Something else happened.");    
                }
            }

            Console.ReadLine(); //Pause
        }
    }
}

Lanch the app, Excel appears and data is written to it. Open the Excel options dialog window from the menu and up pops the error:

An exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll and wasn't handled before a managed/native boundary

Additional information: Exception from HRESULT: 0x800AC472

Click on Continue and my messagege box "All right, what do I do here?" appears.

Please advise?

Best regards, Bertrand

Answer 1

We finally went all the way to Microsoft Support with this issue. Their final response was:

I am able to reproduce the issue. I researched on this further and found that this behaviour is expected and by design. This exception, 0x800AC472 – VBA_E_IGNORE, is thrown because Excel is busy and will not service any Object Model calls. Here is one of the discussions that talks about this. http://social.msdn.microsoft.com/Forums/vstudio/en-US/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/hresult-800ac472-from-set-operations-in-excel?forum=vsto The work around I see is to explicitly catch this exception and retry after sometime until your intended action is completed.

Since we cannot read the minds of the user who might decide to open a window or take a note without realizing the soft has stopped logging (if you mask the error), we decided to work around using:

 xlWorkSheet.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection;

to lock the Excel window UI. We provide an obvious "unlock" button but when the user clicks it, he is sternly warned in a messagebox along with a "Do you wish to continue?"