Deleting Excel WorkSheet using C#

By: Rajesh P.S.

It is possible to delete any worksheet from a Microsoft Excel file programmatically. To achieve this, you need to add a reference to the Microsoft.Office.Interop.Excel assembly in your project. Then, you can utilize classes from that assembly to open the workbook and perform the deletion of the desired worksheet. The program provided below illustrates the process of deleting a worksheet from an existing Excel file using C#.

Excel Library

To access the object model from Visual C# .NET, you have to add the Microsoft Excel 12.0 Object Library to you project. In the previous chapter you can see a step by step instruction on how to add Excel library to your project.

How to add Excel Library

Delete worksheet from an excel file

In order to delete worksheet from excel file, this program open an existing Excel file and select the worksheet and then delete it.

Excel.Sheets worksheets = xlWorkBook.Worksheets; worksheets[1].Delete();

Delete Excel.Worksheet without prompts

Delete Excel.Worksheet without prompt from C#

To suppress prompts and alert messages during the execution of a macro in Microsoft Excel, the DisplayAlerts property can be set to False. By doing so, prompts that require user response are effectively suppressed, and Microsoft Excel automatically selects the default response when necessary. Following the completion of the execution process, Microsoft Excel resets the DisplayAlerts property to True, unless the code being executed is cross-process in nature.

It is important to note that modifying the DisplayAlerts property to False ensures a smooth and uninterrupted execution of the macro, as prompts and alert messages that may interrupt the process are suppressed. Once the macro completes its execution, Microsoft Excel reverts the DisplayAlerts property to its default state, allowing for normal prompt behavior, unless the code executed falls within the scope of cross-process operations.

The following source code shows how to programmatically delete Worksheets from Workbooks.

Full Source C#

using System; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!"); return; } xlApp.DisplayAlerts = false; string filePath = @"d:\test.xlsx"; Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Excel.Sheets worksheets = xlWorkBook.Worksheets; worksheets[1].Delete(); xlWorkBook.Save(); xlWorkBook.Close(); releaseObject(worksheets); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Worksheet Deleted!"); } private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); } finally { GC.Collect(); } } } }

Conclusion

By incorporating these considerations, you can enhance the reliability and seamless operation of your macro by effectively managing prompts and alert messages in Microsoft Excel.

Next > How to format an Excel file using C#

Related Topics

More Related Topics.....