Problem accessing an Excel file from ADO

[Michel S.]

Hello !

I have an Excel 2003 file where I programmed a VBA application.

A one point, the content of another Excel file has to be read and
processed. For some reason, I decided to use some code I had developed
in an Access application to import an Excel sheet data.

The code is using an ADODB connection and recordset and the data is
retreived with an SQL statement.

In short, the code looks like this :

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties='Excel 8.0;HDR=YES'"

rst.Open "SELECT * FROM [Sheet1$]", cnn, adOpenStatic, adLockReadOnly

NB: strFile is a valid and existing file, Sheet1 is an existing sheet
within strFile.

The problem is :

- if that file is already open, everything works fine.

- if the file is not, I get the following error on the rst.Open
statement : 8004005 : DB Engine cannot find 'Sheet1$'

I'm using Office 2003, ADO 2.8 library


The same code works in Access 2002 even with excel not running.


While I can manage to open the file in Excel before and close it after
use, I'd prefer to fix the problem instead of working around it.

Any ideas ?

Thanks

 

[Michel S.]

Well..

I just discovered that, for some mysterious reason, the Excel file was
in 3.0 format ! 8-o

Opening the file in Excel 2003 and saving it again invoked the
conversion dialog. Once saved in the newer format, everything works
fine with the rst.Open statement.

I guess that once loaded in memory, the file was not in 3.0 format,
which explains why it worked only when the file was open.



Michel S. a émis l'idée suivante :