The Microsoft Jet database engine could not find the object Sheet1
 

I have found a problem while accessing Excel spreadsheets via OleDB (I'm not
sure if this is the correct newsgroup for this, but here goes...)

The problem only occurs if I have used filtering in the spreadsheet. Could
anyone tell me if this is a bug? Is there a workaround?

I get the following error when calling connection.GetSchema("Columns");
- The Microsoft Jet database engine could not find the object 'Sheet1$_'.
Make
sure the object exists and that you spell its name and the path name
correctly.

Steps to reproduce:

1. Create a new spreadsheet in Execl 2007 (haven't tried earlier)
2. In A1 type a column heading, e.g. ID
3. In A2 type a value, e.g. 1
4. In A2, enable filtering (Ctrl+Shift+L)
5. Save the file in Excel 97-2003 compatible format.
6. Using .NET 2.0, attempt to get the metadata from the workbook using the
following code:

string ctnStr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" +
excelFile + "; Extended Properties=\"Excel 8.0;IMEX=1\";";

using (OleDbConnection excelCtn = new OleDbConnection(ctnStr))
{
excelCtn.Open();

inputTableInfo = excelCtn.GetSchema("Tables");
inputColumnInfo = excelCtn.GetSchema("Columns"); // fails
here
}

The code fails with the following exception when trying to access the column
metadata:

Type : System.Data.OleDb.OleDbException, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : The Microsoft Jet database engine could not find the object
'Sheet1$_'. Make sure the object exists and that you spell its name and the
path name correctly.
Source : Microsoft JET Database Engine
ErrorCode : -2147467259
Errors : System.Data.OleDb.OleDbErrorCollection
Data : System.Collections.ListDictionaryInternal
TargetSite : System.Data.DataTable GetSchemaRowset(System.Guid,
System.Object[])
Stack Trace : at
System.Data.OleDb.OleDbConnectionInternal.GetSchem aRowset(Guid schema,
Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTa ble(Guid schema,
Object[] restrictions)
at System.Data.OleDb.OleDbMetaDataFactory.PrepareColl ection(String
collectionName, String[] restrictions, DbConnection connection)
at System.Data.ProviderBase.DbMetaDataFactory.GetSche ma(DbConnection
connection, String collectionName, String[] restrictions)
at
System.Data.ProviderBase.DbConnectionInternal.GetS chema(DbConnectionFactory
factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection,
String collectionName, String[] restrictions)
at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName,
String[] restrictionValues)
at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName)

It seems that by enabling filtering, a hidden worksheet is created using the
name of the filtered worksheet followed by a trailing underscore. This
worksheet is listed as one of the "tables" when calling
connection.GetSchema("Tables") but it makes the call to GetSchema("Columns")
fall over.

The main problem I am having is that disabling the filter does not remove
this hidden worksheet. So once filtering is enabled the workbook is useless
to me.

Any help would be appreciated.

Thanks

Stu