c# - Referencing ListObject after undoing Excel table auto expand/resize results in COMException: 'Exception from HRESULT: 0x800A01A8'

Question

I'm working on a sort of a cache of ListObject data and found a possible bug where the ListObject object reference is gone after the user undos a table auto expand/resize.

Steps to reproduce:

1. Create Excel file with a simple table like the one in the image below (the GIF below the image represents the actions leading to possible issue)

2. Create .NET Framework Console App (4.7.2 in my case)

3. Add reference to Microsoft.Office.Interop.Excel (latest version is fine)

4. Copy and paste the following code in Program.cs (it opens Excel, opens the example workbook, subscribes to the SheetChange event and optionally changes the table instead of you - if you want to research the issue manually - steps to reproduce are in the code snippet and see provided GIF after step 1 to view actions leading to issue)

5. Further steps to replicate are mentioned in code comments

    // Steps to replicate bug:
    // 1. Change filePath variable to point to the example file and uncomment the "DoAutomaticChanges()" line (50).
    // 2. Run app, watch the Excel window to view the steps and the SheetChange event handler will report 
    //    System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A01A8' when trying to 
    //    access the ListObject after last cell value change in table.
    //
    // Manual way to replicate bug:
    // 1. Change filePath variable to point to the example file. 
    // 2. Comment out the "DoAutomaticChanges()" line (50).
    // 3. Start the app and let it open the example workbook.
    // 4. Select a couple of cells in the row beneath the table (example range to select D13:E13)
    // 5. Enter "test" and press CTRL + Enter
    // 6. Press CTRL + Z twice (the bug presents itself even if you undo once and try to edit a random table cell)
    // 7. Edit random cell in table and the SheetChange event handler will report 
    //    System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A01A8' when trying to access the ListObject.
    static Worksheet sheet;
    static Application excel;
    static void Main(string[] args)
    {
        var tableName = "ExampleTableName";
        var filePath = @"C:Users...ExampleBook.xlsx";
        excel = new Application
        {
            Visible = true
        };
   
        var wb = excel.Workbooks.Open(filePath);
        sheet = wb.Sheets[1] as Worksheet;
        var listObject = 
            sheet
            .ListObjects
            .OfType<ListObject>()
            .Single(lo => lo.Name == tableName);
   
        wb.SheetChange += (s, e) =>
        {
            var test = listObject.Range;
        };
   
        //DoAutomaticChanges();
        Console.ReadLine();
        wb.Close(false);
        excel.Quit();
    }
   
    private static void DoAutomaticChanges()
    {
        sheet.Range[sheet.Cells[13, 4], sheet.Cells[13, 5]].Select();
        Thread.Sleep(500);
        excel.SendKeys("test^~");
        Thread.Sleep(500);
        excel.SendKeys("^z");
        Thread.Sleep(500);
        excel.SendKeys("^z");
        Thread.Sleep(500);
   
        // this approach represents user interaction and makes the ListObject unavailable
        sheet.Range[sheet.Cells[6, 5], sheet.Cells[6, 5]].Value2 = "test";
   
        // this approach works from code without losing reference of ListObject, but does not represent actual user interaction
        //sheet.Range[sheet.Cells[6, 5], sheet.Cells[6, 5]].Select();
        //excel.SendKeys("test");
    }
   

Is this intended behavior?

I tried to get all tables from the workbook after the exception is thrown and found that a ListObject exists, so my understanding of the issue is that a new table is somehow created instead of the old one when undoing auto expand/resize. Any ideas regarding this issue?

My current workaround -> I store the ListObject's name and when the exception happens I get the ListObject with the same name again from the current workbook.

question from:https://stackoverflow.com/questions/65887196/referencing-listobject-after-undoing-excel-table-auto-expand-resize-results-in-c

Answer 1

Waitting for answers