OLEDB for EXCEL - Drop Table [SheetName$] - Doen't Delete Sheet

Question

I am using drop table [SheetName$] to delete a worksheet from excel.

This just clears the data of the sheet but does not delete the sheet.

I have tried using xls and xlsx. Doesn't work with both versions !

OleDbConnection connection = new OleDbConnection();

try
{
connection.ConnectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='drop.xlsx";
connection.Open();
OleDbCommand command = new OleDbCommand("Drop Table [MySheetName_1$]", connection);
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}

Any Help / Pointers appreciated ! Thanks

Answer 1

Unfortunately, you cannot delete a worksheet using ADO.NET for Excel. Instead, you will need to use the Excel Interop to perform this task. The basic code for the actual DELETE statement would look something like this:

using MSExcel = Microsoft.Office.Interop.Excel;

private MSExcel._Application excel;
private MSExcel._Workbook workbook;
private MSExcel._Worksheet worksheet;
private MSExcel.Sheets sheet;

Excelapp.DisplayAlerts = false;
((Excel.Worksheet)workBook.Worksheets[3]).Delete();
Excelapp.DisplayAlerts = true;

This is the basic rundown of how it would look. The DisplayAlerts lines are to fix an issue some people had with deleting a sheet. Also note that you cannot delete the last sheet in the Excel file. That issue will get you if you don't watch it.

Here are some links to help you out:

MSDN on deleting sheeting in Excel

Post discussing the possibility of using ADO.NET to DROP a sheet in Excel

SO question about deleting a sheet in Excel using the Interop