none
The Microsoft Jet database engine could not find the object 'Sheet1'.

    Question

  • Hi,

    I'm so frustrated.  I cannot connect to a excel sheet.

    I'm trying to build a ms chart program using webforms (VB) (vs2008).  Here's my database file (in chartdata.xls):

    Year Series1 Series2
    1999 5.5 7.5
    2000 2.3 9
    2001 4.7 5.2

    which is in Sheet1 (unmodified sheet name).  I've tried changing this to Sheet1$, but it doesn't make a difference. 

    Here's my code:

    Protected

     

    Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

     

     

    ' Resolve the address to the Access database

     

    Dim fileNameString As String = "chartdata.xls"

     

    ' Initialize a connection string

     

    Dim myConnectionString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileNameString & "; Extended Properties=" & Chr(34) & "Excel 8.0;HDR=Yes" & Chr(34)

     

    ' Response.Write(myConnectionString)

     

    Dim mySelectQuery As String = "SELECT * FROM [Sheet1]"

     

    ' Create a database connection object using the connection string

     

    Dim myConnection As New OleDbConnection(myConnectionString)

     

    ' Create a database command on the connection using query

     

    Dim myCommand As New OleDbCommand(mySelectQuery, myConnection)

     

    ' Open the connection

    myCommand.Connection.Open()

     

    Dim myReader As OleDbDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

    Chart1.DataBindTable(myReader,

    "Name")

     

    ' Close the reader and the connection

    myReader.Close()

    myConnection.Close()

    PLEASE HELP!!!

    THANK YOU!!!

    Thursday, July 08, 2010 5:25 PM

Answers

  • I don't see any syntax issues. You can also get this error if the path to the Excel file is invalid or the Excel file is inaccessible. The connection will appear to work but the SQL statement will fail. Is your Excel file on a network resource?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 09, 2010 6:11 PM

All replies

  • You need to add $ to the name of the spreadsheet inside of your query, like

    Dim mySelectQuery As String = "SELECT * FROM [Sheet1$]"

    Do not change the actual name of the spreadsheet inside of workbook. It still should be Sheet1


    Val Mazur (MVP) http://www.xporttools.net
    Friday, July 09, 2010 10:14 AM
  • I did that, then I get a message that Sheet1$ doesn't exist
    Friday, July 09, 2010 5:17 PM
  • I don't see any syntax issues. You can also get this error if the path to the Excel file is invalid or the Excel file is inaccessible. The connection will appear to work but the SQL statement will fail. Is your Excel file on a network resource?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 09, 2010 6:11 PM