Jamie Collins wrote:

"Joel Gordon" wrote ...

I've successfully used the Jet OLE DB Provider to create a new Excel
worksheet (using a CREATE TABLE)

Not quite correct. You cannot directly create a worksheet using the
OLE DB provider or Excel ODBC driver e.g. (the $ suffix indicates the
Excel 'table' is a worksheet):

CREATE TABLE [Sheet99$] (Col1 FLOAT);

will generate an error, ' 'Sheet99$' is not a valid name because the $
character is illegal in the name for a 'named range'.

You can only directly create a 'named range' (technically a workbook
level defined Name that is defined using a simple formula that returns
a Range object). Try a legal name e.g.

CREATE TABLE Sheet99 (Col1 FLOAT);

and it works without error assuming the a 'named range' does not
already exist. The provider/driver will create a new worksheet to host
the new 'named range', unless a worksheet of the same name already
exists and is unused. If the sheet exists and is 'used' the new sheet
will be given a name derived from the 'named range' name e.g.
Sheet991.

So you can create a worksheet using the provider/driver but only as a
by-product of creating a 'named range' and then only if the
circumstances are favorable.

You can use the DROP syntax with a worksheet e.g.

DROP TABLE [Sheet99$];

This will clear the worksheet including the column headers but it will
not remove the worksheet nor any 'named range' of the same name - the
latter will be redefined to refer to the range in the top left corner
e.g. =Sheet99!$A$1:$A$1. Because the 'named range' is retained, a
subsequent CREATE TABLE using the same table name will fail.

You can also use the DROP syntax with a 'named range' e.g.

DROP TABLE [Sheet99];

this will do the same as DROP on the worksheet name but additionally
remove the defined Name, allowing a subsequent CREATE TABLE to work
and be created on the same worksheet.

Jamie.

--

Hi Jamie,

Thanks for the reply and for clarifying worksheets and named ranges
- I certainly had not understood the differences.

I agree with how you have described the behaviour regarding creating
tables and how this relates to the creation of worksheets and named
ranges BUT...

When I try to drop the table with a 'named range' it does NOT seem to
remove the defined name, hence a subsequent CREATE TABLE does NOT work
- I get an OleDbException stating that the table already exists.

Following is the code I am using. The first time the code is run (and
the excel file does not exist) it runs fine. If it is run again then
the drop table leaves the named range alone and hence the create table
fails.

I am using Microsoft Office Excel 2003 (11.6355.6360).

Am I doing something wrong ?

Thanks,
Joel.

internal static void WriteToExcelFile( DataTable dataTable,
string excelFileName ) {
OleDbConnection connection =
new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=Yes\"" );
if ( File.Exists( excelFileName ) ) {
connection.Open();
if ( TableExists( connection, dataTable.TableName ) ) {
DeleteTable( connection, dataTable.TableName );
}
connection.Close();
}
WriteDataTable( dataTable, connection, excelFileName );
}

private static bool TableExists( OleDbConnection connection,
string tableName ) {
DataTable table =
connection.GetOleDbSchemaTable( OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"} );
foreach( DataRow dataRow in table.Rows ) {
if ( ((string)dataRow["TABLE_NAME"]) == tableName ) {
return true;
}
}
return false;
}

private static void DeleteTable( OleDbConnection connection,
string tableName ) {
OleDbCommand command =
new OleDbCommand( "DROP TABLE [" + tableName + "]",
connection, null );
command.ExecuteNonQuery();
}

private static void WriteDataTable( DataTable dataTable,
OleDbConnection connection,
string fileName ) {
connection.Open();
try {
// Create workbook with appropriate column headers
OleDbCommand command = new OleDbCommand(
GetCreateString( dataTable ), connection, null );
command.ExecuteNonQuery();

// Populate worksheet with all data from the dataTable
OleDbDataAdapter adapter =
new OleDbDataAdapter("SELECT * FROM
["+dataTable.TableName+"]",
connection );
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
adapter.Update( dataTable );
} finally {
connection.Close();
}
}

private static string GetCreateString( DataTable table ) {
string createString = "CREATE TABLE [" + table.TableName + "](\n";
foreach( DataColumn column in table.Columns ){
createString += "[" + column.ColumnName + "] ";
switch( column.DataType.ToString() ){
case "System.String":
createString += "TEXT";
break;
case "System.Int32":
createString += "INT";
break;
case "System.Double":
case "System.Single":
createString += "FLOAT";
break;
case "System.DateTime":
createString += "DATETIME";
break;
default:
throw new ApplicationException( "Unknown type." );
createString += ",\n";
}
createString = createString.TrimEnd( new char[]{'\n',','} );
createString += ");";
return createString;
}