Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 316809 - Last Review: January 31, 2002 - Revision: 1.0
BUG: No ADO Connection Error When Excel Workbook Is Not Found
This article was previously published under Q316809
When you attempt to make an ADO connection to a Microsoft Excel workbook, and the path and file name supplied in the ADO connection string are invalid, or the Excel file is missing, ADO does not generate an error message as expected when the connection fails.
You receive the following error message only when you attempt to open a recordset from the Excel workbook, and the error message does not clearly reveal the underlying cause of the problem:
Runtime error '-2147217865 (80040e37)':
The Microsoft Jet database engine could not find the object 'Sheet1$'.
Make sure the object exists and that you spell its name and the path name correctly.
This behavior occurs with both the Microsoft Jet OLE DB Provider 4.0 and the Microsoft Excel ODBC Driver.
To work around this issue, carefully verify not only the name and spelling of the source table (or tables) named in your query, but the name and spelling of the Excel path and file name supplied in your ADO connection string.
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
Steps to Reproduce the Problem
- Create a new Visual Basic Standard EXE project, and then set a reference to Microsoft ActiveX Data Objects (ADO).
- Place the following code in the Form_Load event of the default Form1.
Note that it references a nonexistent Excel workbook.
Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim strCn As String
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\missing.xls;" & _
"Extended Properties=Excel 8.0"
' strCn = "Driver={Microsoft Excel Driver (*.xls)};" & _
' "DBQ=" & App.Path & "\missing.xls"
cn.Open strCn
Set rs = cn.Execute("SELECT * FROM [Sheet1$]")
End Sub
- Run the project by using the Jet 4.0 Provider.
You receive the error message.
- Switch the active connection string, and then run the project again by using the Excel ODBC Driver.
You receive the error message.
For additional information about the use of ADO with Excel, click the article number below
to view the article in the Microsoft Knowledge Base:
257819Â
(http://kbalertz.com/Feedback.aspx?kbNumber=257819/EN-US/
)
HOWTO: Use ADO with Excel Data from Visual Basic or VBA
APPLIES TO
- Microsoft OLE DB Provider for Jet 4.0
| kbbug kbiisam kbjet kbnofix KB316809 |
Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate