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 :
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