Can not delete a worksheet after upgrading

Question

Can not delete a worksheet after upgrading

Paula Allen 1

I upgraded Visual Studio, and the delete function no longer works. I have tried all the formats I have found on the net, but nothing works.

Paula Allen 1

The code I am now using is

        Excel.Application excelApp = null;
        try
        {
            excelApp = new Excel.Application();
           var wb = excelApp.Workbooks.Open(StrPath);
            if (wb.Sheets.Count >= 3)
            {
                excelApp.DisplayAlerts = false;
                var worksheet = wb.Sheets[3];
                worksheet.Delete();
                excelApp.DisplayAlerts = true;
            };
            wb.Save();
        }
        finally
        {
            excelApp?.Quit();
        };

It falls through the code but does not delete the sheet.

It will insert the new sheet but will not delete the old sheet.

Michael Taylor 60,326 Reputation points

2022-02-09T21:49:59.213+00:00

The code is verifying you have at least 3 worksheets, does your spreadsheet meet that criteria?

You mentioned that it adds new sheets but the code you posted doesn't have any insertion code so where is that code at?

Did you try this on a dummy spreadsheet and did it work correctly?

Paula Allen 1

I will post the insert sheet code.
I did try it on another spreadsheet that was set to meet the criteria.

        if (NewSheetName != ws)
        {

            ////Get the date from the new month box
            //Excel.Sheets sheets = theWorkbook.Worksheets;
            //worksheet = (Excel.Worksheet)sheets.get_Item("Totals");

            //Unprotect the Totals sheet
            worksheet.Unprotect();
            try
            {
                //Add the new sheet after the Totals sheet
                Excel.Worksheet newWorksheet = (Excel.Worksheet)theWorkbook.Worksheets.Add(misValue, worksheet);

                //Rename the sheet for the month and year
                theWorkbook.Worksheets[2].Name = NewSheetName;
                //Set the flag to indicate we inserted the sheet
                success = true;
                //If this is a new year we need to delete Sheet1
                if (isNewYear)
                {
                    theWorkbook.Worksheets["Sheet1"].Delete();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("The worksheet " + NewSheetName + " already exists");
            }
        }

Michael Taylor 60,326 Reputation points

2022-02-09T22:39:57.327+00:00

Ok, so the worksheet may be protected and you're changing that. You add a new worksheet and then if this is a new year you delete Sheet1. Is this deletion code the one that isn't working for you or does Sheet1 get deleted but not another sheet?
Paula Allen 1 Reputation point

2022-02-09T22:43:28.947+00:00

Sheet one does not get deleted. No where in the cod for protecting sheey1.
Jack J Jun 25,296 Reputation points

2022-02-10T06:23:36.487+00:00

@Paula Allen , based on my test, I find that your code can delete the worksheet correctly. Then, what is your current visual studio version? I recommend that you can create a new excel file to test the code to check if it works for anther excel file.
Michael Taylor 60,326 Reputation points

2022-02-10T14:47:07.677+00:00

Please reply to answers using the Comment button instead of creating a new answer. This will help keep your comments threaded to the answer you are replying to. Thanks.
Paula Allen 1 Reputation point

2022-02-10T14:49:11.773+00:00

Sheet1 is the one not getting deleted.
Michael Taylor 60,326 Reputation points

2022-02-10T14:57:20.647+00:00

If deletion fails then you'll get an error. From your description you are saying that the sheet isn't getting deleted but you are able to add a new sheet and you aren't getting any error. That would seem like a logical flow issue then in that the code to delete isn't getting called.

Have you stepped through the code to verify it is being called properly. In the above code I would start with the if statement. Then step line by line and verify each line behaves as expected. After the delete is called then you should be able to save the document and see the change. You can either do this programmatically or via the Immediate window. If the code stepped through properly AND the document saved to disk with the sheet still present then there is clearly something going on with Delete and we need to take a closer look. Otherwise the issue is not with this code but something else (perhaps somebody is adding the sheet back or you have a race condition or something).

I'm particularly interested in that if statement. From my reading it appears you are in a function to add a new worksheet. But that function first checks to see if the "current" worksheet matches by name. If it doesn't then you're adding the new sheet and renaming it. Then if this is a new year then you delete Sheet1. Of course if you're calling this in a loop you'd be adding a bunch of new worksheets with the same name. Also you'd be trying to delete Sheet1 multiple times which would fail.

Michael Taylor 60,326

Given your latest code and making assumptions about what you are trying to do, here is a version that is working correctly for a dummy spreadsheet.

{ 
    Excel.Application excelApp = null;
    Excel.Workbook theWorkbook = null;
    try
    {             
        excelApp = new Excel.Application();

        theWorkbook = excelApp.Workbooks.Open(filename);

        AddWorksheet("Totals", theWorkbook, theWorkbook.Worksheets[1], true);
        theWorkbook.Save();                
    } finally
    {
        theWorkbook?.Close();
        excelApp?.Quit();
    };
}

static bool success;

static void AddWorksheet ( string NewSheetName, Excel.Workbook theWorkbook, Excel.Worksheet worksheet, bool isNewYear )
{
    var ws = worksheet.Name;

    if (NewSheetName != ws)
    {
        ////Get the date from the new month box
        //Excel.Sheets sheets = theWorkbook.Worksheets;
        //worksheet = (Excel.Worksheet)sheets.get_Item("Totals");

        //Unprotect the Totals sheet
        worksheet.Unprotect();

        //Add the new sheet after the Totals sheet
        Excel.Worksheet newWorksheet = (Excel.Worksheet)theWorkbook.Worksheets.Add(After: worksheet);
        //Rename the sheet for the month and year
        theWorkbook.Worksheets[2].Name = NewSheetName;
        //Set the flag to indicate we inserted the sheet
        success = true;
        //If this is a new year we need to delete Sheet1
        if (isNewYear)
        {
            theWorkbook.Worksheets["Sheet1"].Delete();
        };
    }
}

Paula Allen 1 Reputation point

2022-02-10T18:00:19.24+00:00

I will step through the code to check that.

3 answers

Your answer

Paula Allen 1 Reputation point

2022-02-09T21:13:48.07+00:00

The code I am now using is

Excel.Application excelApp = null; try { excelApp = new Excel.Application(); var wb = excelApp.Workbooks.Open(StrPath); if (wb.Sheets.Count >= 3) { excelApp.DisplayAlerts = false; var worksheet = wb.Sheets[3]; worksheet.Delete(); excelApp.DisplayAlerts = true; }; wb.Save(); } finally { excelApp?.Quit(); };

It falls through the code but does not delete the sheet.

It will insert the new sheet but will not delete the old sheet.
Michael Taylor 60,326 Reputation points

2022-02-09T21:49:59.213+00:00

The code is verifying you have at least 3 worksheets, does your spreadsheet meet that criteria?

You mentioned that it adds new sheets but the code you posted doesn't have any insertion code so where is that code at?

Did you try this on a dummy spreadsheet and did it work correctly?
Paula Allen 1 Reputation point

2022-02-09T22:14:24.047+00:00

I will post the insert sheet code.
I did try it on another spreadsheet that was set to meet the criteria.

if (NewSheetName != ws) { ////Get the date from the new month box //Excel.Sheets sheets = theWorkbook.Worksheets; //worksheet = (Excel.Worksheet)sheets.get_Item("Totals"); //Unprotect the Totals sheet worksheet.Unprotect(); try { //Add the new sheet after the Totals sheet Excel.Worksheet newWorksheet = (Excel.Worksheet)theWorkbook.Worksheets.Add(misValue, worksheet); //Rename the sheet for the month and year theWorkbook.Worksheets[2].Name = NewSheetName; //Set the flag to indicate we inserted the sheet success = true; //If this is a new year we need to delete Sheet1 if (isNewYear) { theWorkbook.Worksheets["Sheet1"].Delete(); } } catch (Exception ex) { MessageBox.Show("The worksheet " + NewSheetName + " already exists"); } }
Michael Taylor 60,326 Reputation points

2022-02-09T22:39:57.327+00:00

Ok, so the worksheet may be protected and you're changing that. You add a new worksheet and then if this is a new year you delete Sheet1. Is this deletion code the one that isn't working for you or does Sheet1 get deleted but not another sheet?
Paula Allen 1 Reputation point

2022-02-09T22:43:28.947+00:00

Sheet one does not get deleted. No where in the cod for protecting sheey1.
Jack J Jun 25,296 Reputation points

2022-02-10T06:23:36.487+00:00

@Paula Allen , based on my test, I find that your code can delete the worksheet correctly. Then, what is your current visual studio version? I recommend that you can create a new excel file to test the code to check if it works for anther excel file.
Michael Taylor 60,326 Reputation points

2022-02-10T14:47:07.677+00:00

Please reply to answers using the Comment button instead of creating a new answer. This will help keep your comments threaded to the answer you are replying to. Thanks.
Paula Allen 1 Reputation point

2022-02-10T14:49:11.773+00:00

Sheet1 is the one not getting deleted.
Michael Taylor 60,326 Reputation points

2022-02-10T14:57:20.647+00:00

If deletion fails then you'll get an error. From your description you are saying that the sheet isn't getting deleted but you are able to add a new sheet and you aren't getting any error. That would seem like a logical flow issue then in that the code to delete isn't getting called.

Have you stepped through the code to verify it is being called properly. In the above code I would start with the if statement. Then step line by line and verify each line behaves as expected. After the delete is called then you should be able to save the document and see the change. You can either do this programmatically or via the Immediate window. If the code stepped through properly AND the document saved to disk with the sheet still present then there is clearly something going on with Delete and we need to take a closer look. Otherwise the issue is not with this code but something else (perhaps somebody is adding the sheet back or you have a race condition or something).

I'm particularly interested in that if statement. From my reading it appears you are in a function to add a new worksheet. But that function first checks to see if the "current" worksheet matches by name. If it doesn't then you're adding the new sheet and renaming it. Then if this is a new year then you delete Sheet1. Of course if you're calling this in a loop you'd be adding a bunch of new worksheets with the same name. Also you'd be trying to delete Sheet1 multiple times which would fail.
Michael Taylor 60,326 Reputation points

2022-02-10T15:28:27.323+00:00

Given your latest code and making assumptions about what you are trying to do, here is a version that is working correctly for a dummy spreadsheet.

{ Excel.Application excelApp = null; Excel.Workbook theWorkbook = null; try { excelApp = new Excel.Application(); theWorkbook = excelApp.Workbooks.Open(filename); AddWorksheet("Totals", theWorkbook, theWorkbook.Worksheets[1], true); theWorkbook.Save(); } finally { theWorkbook?.Close(); excelApp?.Quit(); }; } static bool success; static void AddWorksheet ( string NewSheetName, Excel.Workbook theWorkbook, Excel.Worksheet worksheet, bool isNewYear ) { var ws = worksheet.Name; if (NewSheetName != ws) { ////Get the date from the new month box //Excel.Sheets sheets = theWorkbook.Worksheets; //worksheet = (Excel.Worksheet)sheets.get_Item("Totals"); //Unprotect the Totals sheet worksheet.Unprotect(); //Add the new sheet after the Totals sheet Excel.Worksheet newWorksheet = (Excel.Worksheet)theWorkbook.Worksheets.Add(After: worksheet); //Rename the sheet for the month and year theWorkbook.Worksheets[2].Name = NewSheetName; //Set the flag to indicate we inserted the sheet success = true; //If this is a new year we need to delete Sheet1 if (isNewYear) { theWorkbook.Worksheets["Sheet1"].Delete(); }; } }
Paula Allen 1 Reputation point

2022-02-10T18:00:19.24+00:00

I will step through the code to check that.

Answer 1

Michael Taylor 60,326

Please provide more information. VS doesn't do anything with worksheet. Upgrading VS isn't going to change your working code. What delete function are you talking about? If this is related to code then please post that code?

Paula Allen 1 Reputation point

2022-02-09T18:44:25.03+00:00
Sorry for being vague. The code is to delete a sheet in an Excel workbook.

The last try was

Excel.Worksheet worksheets = (Excel.Worksheet)theWorkbook.Sheets[3]; excelApp.DisplayAlerts = false; worksheets.Delete(); excelApp.DisplayAlerts = true;

But I have tried many variations, and nothing seems to work.
I have the following statements.

This code used to work.

using System;
using System.Diagnostics;
using System.IO;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms.DataVisualization.Charting;
using System.Runtime.InteropServices;
using System.Xml;
using System.Collections.Generic;
using System.Xml.Linq;
using OfficeOpenXml;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
Viorel 122.6K Reputation points

2022-02-09T18:51:26.95+00:00

How did you check the result?
Paula Allen 1 Reputation point

2022-02-09T19:03:36.293+00:00

Using the debugger, and looking at the workbook.

Answer 2

Michael Taylor 60,326

Updating VS would not impact this code as you're relying on the OpenXML libraries, I believe. Did you happen to upgrade the NuGet packages that contain OpenXML as well?

I do notice that you're actually using 2 different libraries for accessing Excel. You're using the Excel COM interop assemblies directly and also OpenXML. You don't need both so based upon the code it looks like you're using the Excel COM interop in this block of code. While COM hasn't changed, if you used to use a really, really, really old version of VS then there were changes to COM interop but we're talking back in the VS 2005 days.

The following code works for me using the Excel COM interop.

Excel.Application excelApp = null;
try
{             
    excelApp = new Excel.Application();

    var theWorkbook = excelApp.Workbooks.Open(file);

    if (theWorkbook.Sheets.Count >= 3)
    {
        excelApp.DisplayAlerts = false;

        var worksheet = theWorkbook.Sheets[3];
        worksheet.Delete();
        excelApp.DisplayAlerts = true;
    };

    theWorkbook.Save();
} finally
{                
    excelApp?.Quit();
};

Paula Allen 1 Reputation point

2022-02-09T19:57:15.867+00:00

Tried it and it does not work.
Michael Taylor 60,326 Reputation points

2022-02-09T20:49:37.24+00:00

Then the problem is with other pieces of your code or your spreadsheet.

Take the above code and post it into a new console app. Point it to your spreadsheet you're trying to edit. Does it work? If it does then the issue is with other code in your app that also uses Excel. My gut instinct is that you aren't cleaning up your Excel objects and so you have things sitting around. Multiple instances of Excel is a good indicator.

If it doesn't work then something is special with your spreadsheet. Try a dummy spreadsheet and see if your code works.

As for "it doesn't work", what does it do? Do you get an exception? If so then please the exception and call stack. If it isn't erroring then you know it isn't working because you save the document in your app and then go out to that same location and it hasn't deleted it?

Answer 3

Paula Allen 1

Sorry I have not responded to any of the posts on this subject in a long time as I was in an accident and could not type. I am back now and will review my code, possibly rewriting some of it.

Share via

Can not delete a worksheet after upgrading

3 answers

Your answer