CODEWITHSUNDEEP
c#excel
Martin11175
Martin11175

Reputation: 117

C# Excel add-in worksheet error 0x800A03EC - unrequested cell movement

Alrighty guys I have rather a brain mangler for you; I'm trying develop a relatively simple add-in for excel that should read in data from Sheet A in a workbook, create a new or update sheet B to contain a simplified version of said data in sheet A at the press of a button. Below is some example code I'm working on:

Application.SendKeys("{ENTER}"); // Exit edit mode
Excel.Workbook wb = this.Application.ActiveWorkbook;
Excel.Worksheet sheetA = null;
Excel.Worksheet sheetB = null;

foreach (Excel.Worksheet sheet in wb.Worksheets) {
    // Assume origin sheet we want to move from is same name as book name
    if (sheet.Name == wb.Name)
        sheetA = sheet;

    // Sheet to move to will be called review. Clean if it exists.
    else if (sheet.Name == "Review")
    {
        sheetB = sheet;
        sheetB.Cells.ClearContents();
    }
}

// If origin sheet cannot be found, assume it's the first sheet
if (sheetA == null)
    sheetA = (Excel.Worksheet)wb.Worksheets[1];

// Add the review sheet after the origin sheet if it doesn't exist
if (sheetB == null)
{
    sheetB = wb.Worksheets.Add(After: sheetA);
    sheetB.Name = "Review";
}

// Simply copy across the value of the first cell
sheetB.Range["A1"].Value2 = sheetA.Range["A1"].Value2;

Now the outcomes of this code seem to be radically different depending on whether anything is in "edit mode" (cells are being edited) or not. If not, all is well as you'd expect, a new sheet is created in the correct position and the cell populated.

If a cell is being edited though, the edited cell is moved to another worksheet in the workbook. If there is no other sheet to move to a COMException with HRESULT: 0x800A03EC is thrown (error unknown).

This error shows up A LOT and it's really frustrating with it essentially telling you "be damned if I know", so any ideas would be appreciated. The most common thing seems to be "worksheet doesn't exist" which would be the case here, but I can't tell why it wants to move the edited cell in the first place?

Upvotes: 1

Views: 434

Answers (1)

Martin11175
Martin11175

Reputation: 117

Solution found. Simulating a return key stroke (first line of my example) does exit edit mode, but a delay is required for the application to process it. My implementation worked out as:

Application.SendKeys("{ENTER}"); // Exit edit mode
Timer timer = new Timer(100); // 100ms delay
timer.AutoReset = false; // Stop the timer looping and re-executing
timer.Elapsed += new ElapsedEventHandler((Sender, ent) =>
{
    // Code you want to execute outside of edit mode
});
timer.Start(); // Start her up!

Hope that helps some lost wandering soul!

Upvotes: 1

Related Questions