Matt Goldstein
12/16/2006 12:21:00 AM
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