Get sheet names in Excel95 workbook using OleDB

Question

I am having trouble using the Jet provider and OleDb to get the sheet names in an Excel95 workbook.

I have a workbook with 3 sheets, Sheet1, Sheet2, Sheet3. I am using the following code to extract the sheet name:

var connectionString =
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"", filePath);

using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();
    var dtXlsSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);        
}

This ends up returning a datatable with only one record in it, for Sheet1. I'm guessing there is something wrong with the connection string?

Answer 1

OleDb will not be aware of empty sheets.

As long as the sheets have some data in them, you should get their names.

Answer 2

As per this page, use Excel 5.0 instead of Excel 8.0 in your connection string.

Does that help?

EDIT:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 5.0;HDR=YES;IMEX=1\"", filePath);

EDIT2:

Excel Version: Specify Excel 5.0 for an Excel 95 workbook (version 7.0 of Excel), and Excel 8.0 for an Excel 97, Excel 2000, or Excel 2002 (XP) workbook (versions 8.0, 9.0, and 10.0 of Excel).