Strange problem reading Excel (xls) file with OleDB

[globus2410]

Hi ,

I was looking for how to read an excel (xls) file when i thought to use
ado.net.

This is my code :

System.Data.OleDb.OleDbConnection dbConnection =new
System.Data.OleDb.OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0;"
+ @"Data Source=myFile.xls;"
+ @"Extended Properties=""Excel 8.0;HDR=Yes;""");

dbConnection.Open();

And here's the strange thing : if I have myFile.xls open with Excel
while my code reach the .Open() method everythings is ok.
But if at that time myFile.xls is not opened (with excel) it throws a
"external table different from expected" exception.

What's happening? I don't want to use interop to read an xls file.....
Where am I wrong?

Nicola

 

[W.G. Ryan [MVP]]

I think the problem might be with the IMEX. Here's a connection string I'm
using now that works: (ExcelFile is just a path

String ConnectString;

ConnectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile
+ ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

 

[Nicola]

Thx Ryan but problem is still unsolved....

It looks like that Excel "unlocks" the file when opening it granting
access to that file to OleDb driver.... Or it could be that Excel
itself is a sort of OleDb Server.... argh I'm going crazy....

Nicola

 

[Paul Clement]

On 14 Nov 2006 04:13:12 -0800, (e-mail address removed) wrote:

¤ Hi ,
¤
¤ I was looking for how to read an excel (xls) file when i thought to use
¤ ado.net.
¤
¤ This is my code :
¤
¤ System.Data.OleDb.OleDbConnection dbConnection =new
¤ System.Data.OleDb.OleDbConnection(
¤ @"Provider=Microsoft.Jet.OLEDB.4.0;"
¤ + @"Data Source=myFile.xls;"
¤ + @"Extended Properties=""Excel 8.0;HDR=Yes;""");
¤
¤ dbConnection.Open();
¤
¤ And here's the strange thing : if I have myFile.xls open with Excel
¤ while my code reach the .Open() method everythings is ok.
¤ But if at that time myFile.xls is not opened (with excel) it throws a
¤ "external table different from expected" exception.
¤
¤ What's happening? I don't want to use interop to read an xls file.....
¤ Where am I wrong?

Did you configure the Excel Workbook for shared access?


Paul
~~~~
Microsoft MVP (Visual Basic)