Error accessing certain Excel sheets with Jet

Question

I am using ASP.NET to open an Excel 2003 document hosted on the server. The excel spreadsheet, produced by a system outside of my control, has 5 named worksheets. I access the data in each sheet as follows (line breaks added for readability):

string ExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\sample.xls;
    Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";";

OleDbDataAdapter myData = 
    new OleDbDataAdapter("SELECT * FROM [mysheet]", ExcelConn);
myData.TableMappings.Add("Table", "mysheet");
myData.Fill(ExcelDS);

This works for 3 of the 5 sheets. The other two throw this error:

The Microsoft Jet database engine could not find the object 'mysheet'. Make sure the object exists and that you spell its name and the path name correctly.

I have quadruple-checked the sheet name by examining the text in the bottom tab and also by examining the end of the string that this produces in Excel:

=CELL("filename")
c:\[sample.xls]mysheet

The connection string is specified once and reused for all 5 sheets.

Is it possible that I need to reference the sheet with a string that doesn't match the name that is visible in Excel? Maybe hidden characters, spaces, etc.? Is there another way to find the true name of the sheet? Any other suggestions for retrieving this data?

Note: I cannot modify the Excel document (I would have used SSIS to import CSV if I could have it my way).

I'm using .NET 3.5 / II6.

Answer 1

Well, I found a way to uncover the actual name of the sheet.

Using the connection string in the question, I can do this...

OleDbConnection conn = new OleDbConnection(ExcelConn);
conn.Open();
...
conn.Close();

With the connection open, I can use the debugger to retrieve the sheet names.

conn.GetSchema("Tables").Rows[0]["TABLE_NAME"]
conn.GetSchema("Tables").Rows[1]["TABLE_NAME"]
...
conn.GetSchema("Tables").Rows[9]["TABLE_NAME"]

I found that there were 10 sheet names in the Excel document. The 2 sheets that were causing errors had similar names post-fixed with a "$". I think I'm in for a support nightmare as I'm willing to bet someone played with the Excel document before sending it to me - but at least I know how to pull the data.