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.
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);
}
This is great! Thanks for sharing 🙂
Detection of “cell edit mode” works. But your code didn’t help at the begin. I had to switch from SendKeys.Send to SendKeys.SendWait. And then it WORKED! 🙂
I love you