none
Can't to insert value in EXCEL folder get error RRS feed

  • Question

  •  

    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.

     

    there is a code

     

      

    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.

    {

           string sConnectionString = null;

        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\\Users\\12.xls';Extended Properties=\"Excel 8.0;\"";

           System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(sConnectionString);

           objConn.Open();

     

           //Add two records to the table.

           System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();

           objCmd.Connection = objConn;

           objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" + " values ('Bill', 'Brown')";

           objCmd.ExecuteNonQuery();

           objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" + " values ('Joe', 'Thomas')";

           objCmd.ExecuteNonQuery();

     

           //Close the connection.

           objConn.Close();

    }

    Saturday, August 28, 2010 4:35 PM

Answers

  • Hi Dan_sl0712,

    When you attempt to make an connection to a Microsoft Excel workbook, and the path and file name supplied in the connection string are invalid, or the Excel file is missing the error may occurs. First please 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 connection string.

    Ps: I am moving this thread to ADO.NET Managed Providers forum for better support. Thanks.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, August 31, 2010 8:51 AM
    Moderator
  • I do not think you need to place any single quotes around file path. That maybe reason for the error.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, September 2, 2010 10:26 AM
    Moderator
  • Hi,

    below is the c# code that I refered from microsoft's product support site:

    //where m_strSampleFolder = string variable which has file path; e.g C:\\Users\\MS\\

    // Extended Properties in coonection string are defined as Extended Properties=Excel 8.0; and not like Extended Properties=\"Excel 8.0;\" (I doubt about this one. this could not be reason)

    // Establish a connection to the data source.
    System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
    "Book7.xls;Extended Properties=Excel 8.0;");
    objConn.Open();

    // Add two records to the table named 'MyTable'.
    System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
    objCmd.Connection = objConn;
    objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
    " values ('Bill', 'Brown')";
    objCmd.ExecuteNonQuery();
    objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
    " values ('Joe', 'Thomas')";
    objCmd.ExecuteNonQuery();

    // Close the connection.
    objConn.Close();

    // Insert statement is "Insert into MyTable" for [sheet1$]


    Manish Patil http://patilmanishrao.wordpress.com Posting is provided AS IS with no warranties, and confers no rights.
    Thursday, September 2, 2010 12:34 PM

All replies

  • Hi Dan_sl0712,

    When you attempt to make an connection to a Microsoft Excel workbook, and the path and file name supplied in the connection string are invalid, or the Excel file is missing the error may occurs. First please 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 connection string.

    Ps: I am moving this thread to ADO.NET Managed Providers forum for better support. Thanks.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, August 31, 2010 8:51 AM
    Moderator
  • I do not think you need to place any single quotes around file path. That maybe reason for the error.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, September 2, 2010 10:26 AM
    Moderator
  • Hi,

    below is the c# code that I refered from microsoft's product support site:

    //where m_strSampleFolder = string variable which has file path; e.g C:\\Users\\MS\\

    // Extended Properties in coonection string are defined as Extended Properties=Excel 8.0; and not like Extended Properties=\"Excel 8.0;\" (I doubt about this one. this could not be reason)

    // Establish a connection to the data source.
    System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
    "Book7.xls;Extended Properties=Excel 8.0;");
    objConn.Open();

    // Add two records to the table named 'MyTable'.
    System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
    objCmd.Connection = objConn;
    objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
    " values ('Bill', 'Brown')";
    objCmd.ExecuteNonQuery();
    objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
    " values ('Joe', 'Thomas')";
    objCmd.ExecuteNonQuery();

    // Close the connection.
    objConn.Close();

    // Insert statement is "Insert into MyTable" for [sheet1$]


    Manish Patil http://patilmanishrao.wordpress.com Posting is provided AS IS with no warranties, and confers no rights.
    Thursday, September 2, 2010 12:34 PM