ComException When trying to delete a worksheet from an Excel File in C#

Question

I'm running into an Odd problem that I cant seem to find a solution for. I'm trying to delete any work sheet in a workbook that contains a certain worksheet name and I keep getting this exception: "Unable to get the Delete property of the Worksheet class" I'm able to use the worksheet class in other places but when i try to use the Delete(); method, or even the one that sets the visibility of said sheet I get the same thing. Any help as to why would be appreciated!

Microsoft.Office.Interop.Excel.Sheets TestWorksheets = TestBook.Worksheets;
if (TestWorksheets.Count > NumberofsheetsIWantToKeep)
{
   int WorkSheetCounter = TestWorksheets.Count;
   while (WorkSheetCounter > NumberofsheetsIWantToKeep)
   {
      if(TestWorksheets[WorkSheetCounter].Name.Contains("blah"))
      {
        TestWorksheets[WorkSheetCounter].Delete();
      }
      WorkSheetCounter--;
   }
}

@MethodMan Thanks for the reply! I did see that when I was looking for a solution, and from my understanding I do believe that I am deleting a worksheet the way they specified, yet it still is not working. That's my problem! — Vincep92, Oct 04 '16 at 20:34
use a for loop and a counter / index if the worksheet matches that you are looking for based on the link that I have posted earlier then you should be able to take the 1 line and make it into a for loop using the same approach. I hope this makes more sense — MethodMan, Oct 04 '16 at 20:36
@MethodMan I think you might be misunderstanding my question. I don't think my loop is the part I'm trying to fix. It's the fact that I get an exception when I call the Delete(); for the worksheet class. My loop properly stops when I find something I want but when I want to actually delete the worksheet I get the exception. The exception is the part I'm confused about. — Vincep92, Oct 04 '16 at 20:41
Ok I see what you're saying .. google this error the `C# Unable to get the Delete property of the Worksheet class` — MethodMan, Oct 04 '16 at 20:56
@MethodMan I have, and the only really useful result I found was that: the error usually occurs when any argument passed to the worksheet function is not of the correct type or simply doesn't make sense (I did not come up with that, I got it from the answer from this question: http://stackoverflow.com/questions/10635048/excel-error-1004-unable-to-get-property-of-worksheetfunction-class-appear). However, in my case I'm deleting the way that I've found works for most people online and I'm still getting this exception, which is my problem. — Vincep92, Oct 04 '16 at 21:16

score 0 · Accepted Answer · answered Oct 04 '16 at 22:53

0

turns out I had to activate the workbook and the worksheet before I could delete it. I feel silly and apologize for that!

answered Oct 04 '16 at 22:53

Vincep92

1
3

1

Don't feel silly and don't apologize, it's the documentation authors who should apologize for not mentioning this. In my case, activating the workbook and worksheets don't help. I still get 0x800A03EC ("automation error"). That does not mean anything, I give up. How does anyone ever debug Excel errors except by pure chance? – Eldritch Conundrum Aug 16 '19 at 12:15

score 0 · Answer 2 · edited Apr 20 '17 at 09:39

0

use below code instead of directly deleting. Activate workbook and worksheet.

   Microsoft.Office.Interop.Excel.Workbook book = TestBook;
   book.Activate();
   TestWorksheets[WorkSheetCounter].Activate();
   TestWorksheets[WorkSheetCounter].Delete();

edited Apr 20 '17 at 09:39

nbar

6,028
2
24
65

answered Apr 20 '17 at 09:30

Shrikant

39
4

score 0 · Answer 3 · answered Sep 18 '19 at 14:28

I had a similar issue this morning while processing an XLSX file to clean it of named references and a macro worksheet. When attempting to Delete() the sheet it would fail with error: Exception from HRESULT: 0x800A03EC. Found that even with Document Inspector in Excel could not remove the hidden worksheet. Further investigation led me to discover the sheet's visibility was set to VeryHidden. Changing the visibility to something other than VeryHidden before attempting to delete the worksheet resolved my problem.

Using the original example code from above this is what I needed to get the delete to not error.

Microsoft.Office.Interop.Excel.Sheets TestWorksheets = TestBook.Worksheets;
if (TestWorksheets.Count > NumberofsheetsIWantToKeep)
{
    int WorkSheetCounter = TestWorksheets.Count;
    while (WorkSheetCounter > NumberofsheetsIWantToKeep)
    {
        if(TestWorksheets[WorkSheetCounter].Name.Contains("blah"))
        {
            if(TestWorksheets[WorkSheetCounter].Visible == Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVeryHidden)
            { TestWorksheets[WorkSheetCounter].Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden; }
            TestWorksheets[WorkSheetCounter].Delete();
        }
        WorkSheetCounter--;
    }
}

ComException When trying to delete a worksheet from an Excel File in C#

3 Answers3