Jet error message when trying to create a DS from Excel

28 Aug 2006 3:48 PM

Gordon

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 !

--
Gordon

30 Aug 2006 3:42 PM

Paul Clement

On Mon, 28 Aug 2006 08:48:01 -0700, Gordon <Gor***@discussions.microsoft.com> wrote:

¤ 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)