Jet error message when trying to create a DS from Excel

[Guest]

Hi;

What can I not fill my dataset once I have connected to my Excel ss.

When I get to the fill ds part of my code I get the following exception "

"The Microsoft Jet database Engine could not find the object "tab1". Make
sure the object exists and that you spell the name and path correctly."

I know that I have the correct excel sheet and that the path is correct so
why doesn't the engine like this !!!!!!

daTrust = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [unit_difference]", Conn)

unit_difference is the "named" part of the spreadsheet.

Here is the code I am trying to use:

Option Explicit On
Imports System
Imports System.IO
Imports System.data
Imports System.Data.OleDb

Module ptr423Late

Dim sPath As String = "C:\Desktop\657423.mdb"
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & sPath & ";Persist Security Info=False"
Dim Conn As OleDbConnection = New OleDbConnection(connString)
Dim holdCreaTxtFl As Date
Dim dteToCompare As Integer
' Dim holdFlNme As String = "\\mgmtrust\trust1\reports\trust
Accounting\PTR423_2006.xls"
Dim holdFlNme As String = "c:\Documents and
Settings\desktop\657423_08252006.xls"
Dim dsTrust As System.Data.DataSet
Dim daTrust As OleDb.OleDbDataAdapter



Sub main()

' To do Import 657423_2006.xls in to an access

Conn = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=holdFlNme; " & _
"Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.
Try
conn.Open()
daTrust = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [unit_difference]", Conn)
dsTrust = New System.Data.DataSet
daTrust.Fill(dsTrust)

Catch ex As Exception
MsgBox("Exception Ocurred :" + ex.Message.ToString())
End Try

End Module

Thanks for your insights !

 

[Paul Clement]

¤ Hi;
¤
¤ What can I not fill my dataset once I have connected to my Excel ss.
¤
¤ When I get to the fill ds part of my code I get the following exception "
¤
¤ "The Microsoft Jet database Engine could not find the object "tab1". Make
¤ sure the object exists and that you spell the name and path correctly."
¤
¤ I know that I have the correct excel sheet and that the path is correct so
¤ why doesn't the engine like this !!!!!!

You may need to append a dollar sign to the sheet name in order for it to be recognized by the data
access mechanism.

SELECT * FROM [Sheet1$]


Paul
~~~~
Microsoft MVP (Visual Basic)