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 following exception:

System.Runtime.InteropServices.COMException with HRESULT: 0x800AC472

This 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 message box "All right, what do I do here?" appears.

Please advise?

Best regards, Bertrand

Richel

All right, what do I do here? - if you can't avoid the exception from being thrown then you need a way to handle it. The only solution I found to this error code was to loop and wait until the exception went away or I thought about disabling the main menu of Excel while writing to it, but cannot find a reference on how to do this. - can you not go Full Screen in Excel or disable the ribbon? – Vulnerable

Sounds like deep doo-doo. If you are willing to disable the menu, an alternative means would be to create a 99% transparent sneeze guard that follows the excel window around, but that still leaves the question of keyboard access. I would say the best solution is to throw up a tooltip type notice to let the user know you are in your exception loop. – Undo

me how: you get the same error in older versions of Excel with no ribbon by opening the About window, so it's not a ribbon issue. – Richel

Mark Robbins: disabling the menu is a possible but undesirable solution. I would like to resolve the exception, really. – Richel

The problem with excel is each work book is single threaded so it cannot work asynchronously. As others have mentioned you are best off locking the user from the spreadsheet while you work. or pup up a message that says please dont do that at the moment I am working – Spectra

Another way you can get Excel to enter the problematic "Object Model is Suspended" state is to press and hold down your mouse button on the sheet. – Inconsequential

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?"

Jae

Answer 2

Make Excel Interactive is a perfect solution. The only problem is if the user is doing something on Excel at the same time, like selecting range or editing a cell. And for example your code is returning from a different thread and trying to write on Excel the results of the calculations. So to avoid the issue my suggestions is:

private void x(string str)
{
    while (this.Application.Interactive == true)
    {
        // If Excel is currently busy, try until go thru
        SetAppInactive();
    }

    // now writing the data is protected from any user interaption
    try
    {
        for (int i = 1; i < 2000; i++)
        {
            sh.Cells[i, 1].Value2 = str;
        }
    }
    finally
    {
        // don't forget to turn it on again
        this.Application.Interactive = true;
    }
}
private void SetAppInactive()
{
    try
    {
        this.Application.Interactive = false;
    }
    catch
    {
    }
}

Knout

Answer 3

xlApp = new Excel.Application();
xlApp.Interactive = false;

Femmine

Code-only answers are considered low-quality here. Please add some explanation to your answer instead of just posting a snippet. – Interglacial

Answer 4

What I have done successfully is to make a temp copy of the target excel file before opening it in code.

That way I can manipulate it independent of the source document being open or not.

Dispel

Answer 5

One possible alternative to automating Excel, and wrestling with its' perculiarities, is to write the file out using the OpenXmlWriter writer (DocumentFormat.OpenXml.OpenXmlWriter).

It's a little tricky but does handle sheets with > 1 million rows without breaking a sweat.

OpenXml docs on MSDN

Etra

ClosedXML (closedxml.codeplex.com) is a nice wrapper library around the Open XML SDK. – Inconsequential

Answer 6

Since Interop does cross threading, it may lead to accessing same object by multiple threads, leading to this exception, below code worked for me.

bool failed = false;
do
{
    try
    {
        // Call goes here
        failed = false;
    }
    catch (System.Runtime.InteropServices.COMException e)
    {
        failed = true;
    }
    System.Threading.Thread.Sleep(10);
} while (failed);

Mudra