Excel C# interop worksheet delete

Question

I have an app using Excel COM interop. It copies a template XLS to an existing doc, replacing same-named tabs by Delete old then Copy new from template.

I am getting 800A03ec exception when my app tries to delete a worksheet.

This problem is in code that has been working for years but now fails after an upgrade to Office 2016.

I find that if I set app.Visible = true, the operation completes properly! But I do not want Excel visible.

If app.Visible = false, I do not get an error from the first worksheet Delete(), but exception occurs on the second.

The first delete of the last tab seems to go OK, but 'Sheets' array of the worksheets object doesn't decrease as I would expect. However the corresponding Sheet item in the array becomes a "null" worksheet.

The second delete, of the tab before it, throws an exception on delete.

I have thoroughly ensured:

No COM reference leaks
All COM references are discarded before each delete, except for the worksheet to be deleted and its parent app, etc. objects
DisplayAlerts is false
Worksheets are simple and ordinary, nothing hidden etc.

Why would it work when app is visible, but not work when app is hidden??

UPDATE: code fragment

int _DeleteTargetTab(string tabName)
{
    List<object> comRefs = new List<object>();

    int prevTabIndex;
    int tabToDelete = _FindTargetTemplateIndex(tabName, out prevTabIndex);

    if (tabToDelete > 0)
    {
        try
        {
            Excel.Sheets targetSheets = _workbook.Sheets;
            comRefs.Add(targetSheets);

            Excel.Worksheet targetWorksheet = targetSheets[tabToDelete];
            comRefs.Add(targetWorksheet);

            targetWorksheet.Delete();
        }
        finally
        {
            ExcelUtility.ReleaseAll(comRefs);
        }
    }

    return prevTabIndex;
}

and ExcelUtility.ReleaseAll() calls Marshal.ReleaseComObject(), then GC.Collect() and GC.WaitForPendingFinalizers().

Target worksheet has 8 tabs. Last tab is deleted, then copied, without exception. Then on tab 7 delete throws an exception, only when app is hidden. Works fine on older Office.

Copy code is

void _CopyTemplateTabToTarget(Excel.Worksheet templateWorksheet, int prevTargetTabIndex)
{
    List<object> comRefs = new List<object>();

    try
    {
        Excel.Sheets targetSheets = _workbook.Sheets;
        comRefs.Add(targetSheets);

        Excel.Worksheet prevSheet = targetSheets[prevTargetTabIndex];
        comRefs.Add(prevSheet);

        templateWorksheet.Copy(Type.Missing, prevSheet);
    }
    finally
    {
        ExcelUtility.ReleaseAll(comRefs);
    }
}

Init code is

        _app = new Excel.Application();
        _app.DisplayAlerts = false;
        _app.Visible = false;

The error indicates a name not found type of error. Without seeing some code, speculation is simply a waste of time. — JohnG

jws jws · Accepted Answer · 2017-01-09T13:04:55

I got further by adding this:

//
// Super important to activate a tab other than what needs to be deleted.
// Cast is required because an event and method have the same name "Activate".
//

((Excel._Worksheet)_weeklyDataSheet).Activate();

Then that got me to some code around my apps' Save function that used to work but was throwing exception:

_weeklyDataSheet.Select(Type.Missing);

I changed that to Activate() as well and made more progress. But yet Excel still threw exception on Delete() this time on the third workbook.

I was forced to run app Visible for the week's report. But even that ran through about 80 workbooks and then Excel locked up, mouse cursor rapidly flashing between arrow and wait timer animation, and my app got RPC error eventually.

Conclusion: ABANDON COM APIS for Office 2016. Microsoft seems not to support them properly anymore.

Excel C# interop worksheet delete

1 Answers