Exceptions Occur When Automating Excel / Detecting Cell Edit Mode


Sometimes when your are Automating Excel, you may see an exception similar to the following:

System.Runtime.InteropServices.COMException was unhandled
  Message="Exception from HRESULT: 0x800A03EC"
  Source="Microsoft.Office.Interop.Excel"
  ErrorCode=-2146827284

This may seem random and may not seem to occur on your system. Well, this is a fairly common issue I have experienced in the field. And, there is not a lot of good information about it on the web. Until now…

The problem occurs because Excel is in edit mode. The user has a cell selected and text currently being typed into it, but they have not yet clicked out of the cell.

Note, when this happens Excel grays out the Ribbon items and most options are unavailable.

Well, there really is not an easy way to determine this state for Excel, so  the following code can be used to determine if Excel is in Edit Mode and also to get it out of Edit mode. The only call you need to make is exitEditMode(). If Excel is OK, nothing happens; however, if it is in edit mode, it pops it out so your automation code can continue.

[DllImport("User32.dll")]
public static extern Int32 SetForegroundWindow(int hWnd);

Excel.Application xlApp;
public Form1()
{
    InitializeComponent();
    xlApp = new Excel.Application();
    xlApp.Visible = true;
}

private void button1_Click(object sender, EventArgs e)
{
    exitEditMode(); // the only call you need to make
}

private void exitEditMode()
{
    if (!isExcelInteractive())
    {
        // get the current range
        Excel.Range r = xlApp.ActiveCell;
        // bring Excel to the foreground, with focus
        // and issue keys to exit the cell
        xlBringToFront();
        xlApp.ActiveWindow.Activate();
        SendKeys.Flush();
        SendKeys.Send("{ENTER}");
        // now make sure the original cell is
        // selected…
        r.Select();
    }
}

private bool isExcelInteractive()
{
    try
    {
        // this line does nothing if Excel is not
        // in edit mode. However, trying to set
        // this property while Excel is in edit
        // cell mdoe will cause an exception
        xlApp.Interactive = xlApp.Interactive;
        return true; // no exception, ecel is
                     // interactive
    }
    catch
    {
        return false; // in edit mode
    }
}

private void xlBringToFront()
{
    SetForegroundWindow(xlApp.Hwnd);
}

3 thoughts on “Exceptions Occur When Automating Excel / Detecting Cell Edit Mode

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s