Problem using CColumn of OLE DB on Excel Spreadsheet

[Lim Say Kiap]

I am using OLE DB to read excel spreadsheet. After connection has been made, and a worksheet has been specified by calling the CColumn::Open(), I would use the CColumn::MoveFirst() and and CColumn::MoveNext()to retrieve the all the columns information of a the selected worksheet.

This works perfectly fine for normal worksheets. However for worksheets that have names starting with numeric (eg 1Sheet1) or has a dash (eg Sheet-1) in the name, the CColumn::MoveFirst() returns DB_S_ENDOFROWSET. Does anyone knows what is the problem?

Thanks.

[Lim Say Kiap]

I will provide more details of the problem.

I am using Jet OLEDB 4.0 to access excel files. It seems that it will not work if the worksheet name of a excel file starts with a numeric but works fine otherwise. For example if the worksheet name is 1Sheet1, it will not work but if the worksheet name is Sheet1, it will work fine.



The code is as follows:



//Start of Code

CDataSource _datasource;

CSession _session;

CColumns* _columns



_datasource.OpenFromInitializationString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Test.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=2';"", false);



_session.Open(_datasource);



_columns->Open(_session, NULL, NULL, "Sheet1$", NULL);

//_columns->Open(_session, NULL, NULL, "1Sheet1$", NULL);



HRESULT hr = _columns->MoveFirst();



//End of code



For the code shown above, if i use

_columns->Open(_session, NULL, NULL, "Sheet1$", NULL);, the _columns->MoveFirst(); will return S_OK.



But if i use _columns->Open(_session, NULL, NULL, "1Sheet1$", NULL);, the _columns->MoveFirst(); will return DB_S_ENDOFROWSET even though i have a worksheet named 1Sheet1 in Test.xls



This also happens if the worksheet name has a "-" (eg Sheet-1). Does anybody know what is the problem?