locked
Drop Table in Excel does not work RRS feed

  • Question

  • I work with Microsoft Office 2003.  I need to dynamically create new sheets in Excel file based on the template and populate them, and at the end of the process delete template sheet.
    Creation of the new sheets works fine with the "create table" sql statement, but the "drop table" command only clears the content of the template sheet and does not remove the sheet itself.

    Here is a function I wrote in C#:
            private static void DeleteExcelSheet(string fileName, string tableName)
            {

                System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();

                string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + @";Extended Properties=""Excel 8.0;HDR=YES;Readonly=False;IMEX=0;"";";
                conn.ConnectionString = connString;
                conn.Open();

                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
                cmd.Connection = conn;

                string sql = "DROP TABLE [" + tableName + "$]";

                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                conn.Close();

            }

    Please help!
    Wednesday, July 8, 2009 5:22 PM

Answers

  • Jet OLEDB provider has no full support for the DROP statement when it comes to Excel files. This is limitation and if you really need to delete it, you could use DOM model from Visual Studio Tools for Office, which would require Excel installed on client PC where you run application. I have not tried to use ACE OLEDB provider, but most likely it has same limitations.
    Val Mazur (MVP) http://www.xporttools.net
    • Proposed as answer by VMazur Thursday, July 9, 2009 10:32 AM
    • Marked as answer by Yichun_Feng Friday, August 7, 2009 5:46 AM
    Thursday, July 9, 2009 10:32 AM