Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem accessing an Excel file from ADO
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solved: Problem accessing an Excel file from ADO
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem accessing Pivot table Excel 2007 | Excel Worksheet Functions | |||
accessing word file from an excel file | Excel Programming | |||
asp having problem accessing excel 2002 | Excel Programming | |||
Multiple accessing of an excel file | Excel Discussion (Misc queries) | |||
accessing .mpp file using excel VBA macros | Excel Programming |