I can't get get the delete sheets working

Question

0.00/5 (No votes)

See more:

I am doing a "saveas" to create the archive workbook then I need to delete all the data sheets in the main foodpantry.xlsx workbook. The delete sheet does not seem to work. The code I am using is as follows. The teap# variables are used to see what is going on.

private string CreateArchiveFileName(string archiveFileDate)
        {
            //Strip the month so all that is left is the year
            archiveFileDate = archiveFileDate.Substring(4, 4);
            strArchiveFileName = "Food Pantry Register " + archiveFileDate;
            //Get the current path
            archiveWorkbookPath = frmFoodPantry.theWorkbook.Path;
            //Set the path for the archive (this directory must have been already created)
            archiveWorkbookPath = archiveWorkbookPath + @"\Archive\";
            //set the file name
            strArchiveFileName = strArchiveFileName + ".xlsx";
            //Set the full path
            strArchiveFileName = archiveWorkbookPath + strArchiveFileName;
            return strArchiveFileName;
        }
        private void FoodPantryFileDeleteSheets()
        {
            int sheetsToDelete = frmFoodPantry.theWorkbook.Sheets.Count;
string temp1 = frmFoodPantry.objApp.ActiveWorkbook.Name;
            //Microsoft.Office.Interop.Excel.Sheets worksheets = frmFoodPantry.theWorkbook.Worksheets;
            for (int i = 2; i < sheetsToDelete; i++)
            {
                frmFoodPantry.theWorksheet.Select(i);
                //frmFoodPantry.objApp.ActiveSheet(1);
                //frmFoodPantry.objApp.SheetActivate;
string temp2 = frmFoodPantry.objApp.ActiveSheet.Name;
                //var worksheets = frmFoodPantry.objApp.ActiveSheet;
string temp3 = frmFoodPantry.objApp.ActiveWorkbook.Name;
                //worksheets[2].Delete();

                ((Excel.Worksheet)frmFoodPantry.objApp.ActiveWorkbook.Sheets[i]).Delete(); 
string temp4 = frmFoodPantry.objApp.ActiveSheet.Name;


            }
            frmFoodPantry.objApp.DisplayAlerts = false;
            frmFoodPantry.theWorkbook.SaveAs(frmFoodPantry.theWorksheet.Name);
string temp5 = frmFoodPantry.objApp.ActiveWorkbook.Name;
            frmFoodPantry.objApp.DisplayAlerts = true;

        }

What I have tried:

All the Microsoft suggestions.
See commented-out lines

 {
                frmFoodPantry.theWorksheet.Select(i);
                //frmFoodPantry.objApp.ActiveSheet(1);
                //frmFoodPantry.objApp.SheetActivate;
string temp2 = frmFoodPantry.objApp.ActiveSheet.Name;
                //var worksheets = frmFoodPantry.objApp.ActiveSheet;
string temp3 = frmFoodPantry.objApp.ActiveWorkbook.Name;
                //worksheets[2].Delete();

Posted 8-Aug-22 1:05am

PaulaJoannAllen

Updated 8-Aug-22 6:46am

v2

Add a Solution

Comments

0x01AA 8-Aug-22 7:43am

1.) Does it delete nothing?
2.) Think about that
You do for (int i = 2; i < sheetsToDelete; i++) and delete the current the sheet with index 'i' and then 'i will be incremented in the for statement....
Better do somthing like


while (worksheets.Count  >= 3) // 3 or whatever
  worksheets[worksheets.Count].Delete();

It makes sense to put this before the delete to avoid user messages
objApp.DisplayAlerts = false;

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

0x01AA · Accepted Answer · 2022-08-08T02:21:00

The below code works for me and does delete sheets as long there are more than three sheets.

private void buttonDeleteSheets_Click(object sender, EventArgs e)
{
    // Excel Application
    Microsoft.Office.Interop.Excel.Application _excel = new _Excel.Application();

    // No User Intervention Dialogues
    _excel.DisplayAlerts = false;

    // Open workbook
    Workbook workbook = _excel.Workbooks.Open(@"c:\temp\cp.excel\ExcelDeleteTemplate.xlsx");


    // Delete
    while (workbook.Sheets.Count > 3)
    {
        workbook.Sheets[workbook.Sheets.Count].Delete();
    }

    // SaveAs the workbook
    workbook.SaveAs(@"c:\temp\cp.excel\ExcelDeletedSheets.xlsx");

    // Close
    workbook.Close();
}

I can't get get the delete sheets working

1 solution

Solution 1

Add your solution here

Preview 0

I can't get get the delete sheets working

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us