locked
How to update single cell of excel sheet using oledb connection, RRS feed

  • Question

  • User573053752 posted

     Friends i got some codes from net  by using that i can update a excel sheet ,but there we have to specify some header fields name in command string like as fallows ,

    Update [Sheet1$] set ProductName="IBG" where ProductId=5

    but i want to update a single cell of excel sheet only,let say O12 cell i want to change somedata,how it is possible that i don't know.plz help me out...

     

    thanks in advance...

     

     

    Saturday, February 2, 2008 8:10 AM

Answers

  • User187056398 posted

    I think you are misunderstanding the syntax.  Here is another example:

            String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Book1.xls;Extended Properties='Excel 8.0;HDR=NO'";
            OleDbConnection objConn = new OleDbConnection(sConnectionString);
            objConn.Open();
            OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1=123456", objConn);
            objCmdSelect.ExecuteNonQuery();
            objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A4:A4] SET F1='hello'", objConn);
            objCmdSelect.ExecuteNonQuery();
            objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A5:A5] SET F1='goodby'", objConn); 
            objCmdSelect.ExecuteNonQuery();
    
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 4, 2008 8:47 PM

All replies

  • User187056398 posted

    This link may help you:

    http://www.pcreview.co.uk/forums/thread-1176677.php

     

    To update a single cell in an existing table you use
    HDR=No in the extended properties of the connection string and use
    UPDATE sql syntax such as:

    UPDATE [Sheet3$A2:A2] SET F1='TestValue1';

    F1 is the default name Jet assigns to the first column
    where the column name is unknown or invalid. The inserted value must
    match the data type for the *whole* column.

    Saturday, February 2, 2008 2:37 PM
  • User573053752 posted

      Thanks for your replay i have tried but some error is comeing ,i have written as fallows,

     String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0;HDR=NO'";
                OleDbConnection objConn = new OleDbConnection(sConnectionString);
                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1='TestValue1'", objConn);

                objCmdSelect.ExecuteNonQuery();
                objConn.Close();

     the error is="No value given for one or more required parameters."

     

    here i am not getting where is the error plz help me ,plz give me some codes to do update a single cell of excel sheet. 

    Saturday, February 2, 2008 11:58 PM
  • User187056398 posted

    This worked on my machine (after I changed the update value from text to int so it would match the cell type)

    String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Book1.xls;Extended Properties='Excel 8.0;HDR=NO'";
    OleDbConnection objConn = new OleDbConnection(sConnectionString);
    objConn.Open();
    OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1=123456", objConn);
    
    objCmdSelect.ExecuteNonQuery();
    objConn.Close();
    
     
    Sunday, February 3, 2008 10:00 AM
  • User573053752 posted

    Thanks  for ur reply,

    this code is working fine but only for the f1 column but i want to change cell value of f12 or f13 then this gives same error that i have posted before, plz give me the command for updateing f12 or f13 cell of the excel file. 

    Sunday, February 3, 2008 11:10 PM
  • User187056398 posted

    I think you are misunderstanding the syntax.  Here is another example:

            String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Book1.xls;Extended Properties='Excel 8.0;HDR=NO'";
            OleDbConnection objConn = new OleDbConnection(sConnectionString);
            objConn.Open();
            OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1=123456", objConn);
            objCmdSelect.ExecuteNonQuery();
            objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A4:A4] SET F1='hello'", objConn);
            objCmdSelect.ExecuteNonQuery();
            objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A5:A5] SET F1='goodby'", objConn); 
            objCmdSelect.ExecuteNonQuery();
    
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 4, 2008 8:47 PM
  • User526805926 posted

    Can I use the above oleDbCommand to update a control (like checkbox to checked/unchecked) in a cell.

    Monday, March 16, 2009 4:37 PM
  • User86136512 posted

    It works only for F1 column, Not working for F2 F3 F4... columns

                commandString = "UPDATE [MBS$A2:A2] SET F1=123456";
                cmdExcel.CommandText = commandString;

                cmdExcel.ExecuteNonQuery();
                
                commandString = "UPDATE [MBS$A4:A4] SET F2='hello'";
                
                cmdExcel.CommandText = commandString;

                cmdExcel.ExecuteNonQuery();

                commandString = "UPDATE [MBS$B5:B5] SET F2='goodby'";

                cmdExcel.CommandText = commandString;

                cmdExcel.ExecuteNonQuery();


    Please tell me syntax to update F2 F3 columns cells.



    Thanks,

    Abhijeet.

    Monday, December 14, 2009 4:02 AM
  • User-1760785997 posted

    Old post so probably not necessary anymore but here's the answer anyway:

    Your "destination range" is defined as "[MBS$A2:A2]" which simply selects the range "A2:A2" in sheet "MBS" for update. That range only contains one cell, which is why you can only update the field "F1". If you want to update "F2" you probably mean you want to update cell A3 in Excel so you need to expand your destination range to "[MBS$A2:A3]".

    So with this example you can use F2:

    UPDATE [MBS$A2:A3] SET F1=123456, F2='hello'

    You obviously could have done this with two update statements as well, but this would be more performant.

    Remember that the automatic naming of columns (F1, F2, F3,...) has nothing to do with the Excel cells F1, F2, F3. F1 is just the first cell in your destination range...

    Friday, February 19, 2010 5:25 AM
  • User560957079 posted

    Hi,


    I'm trying to update excel 2007 (*.xlsx) using the statement below

    "UPDATE [Sheet1$A5:A5] set F1=999"

    The issue now, it always updated the first row, in this case is A1.

    Another example below:

    "UPDATE [Sheet1$E5:E5] set F1=999"

    It will update E1.


    Appreciate if someone can help me how to solve this issue. 


    Monday, April 5, 2010 6:37 AM
  • User-487199789 posted

    @herism82


    I am having the exact same problem.

    Have you found a solution for your problem ?

    As long as my xlsm file is closed it only updates the first row. When the file is open when I run my program it works fine ...

    Friday, July 30, 2010 8:39 AM
  • User498195007 posted

    Hi,

     

    I'm trying to update excel 2007 (*.xlsx) using the statement below

    "UPDATE [Sheet1$A5:A5] set F1=999"

    The issue now, it always updated the first row, in this case is A1.

    Another example below:

    "UPDATE [Sheet1$E5:E5] set F1=999"

    It will update E1.

     



    Have you solved this problem ??

    If solved Please Help me !!

    I'm also facing the same problem

    Thursday, October 21, 2010 7:42 AM
  • User-487199789 posted

    Hi Sameer,

    I did have the exact same problem a few months ago while working on the proof of concept.
    A week ago i continued working on this project and no longer seem to be having this issue.

    And i'm afraid i can't say what caused the problem to dissapear.
    I'm guessing it must either have been after some windows update or possibly after rebooting a few times.

    I wish i could give you a sufficient answer but i'm afraid my knowlegde is lacking ...

    Thursday, October 21, 2010 7:50 AM
  • User498195007 posted

    Hi kverheire,

    Thanks for your Reply..

    I also rebooted & updated my OS But it didn't work.

    Appreciate it thanks a lot

    Thursday, October 21, 2010 9:04 AM
  • User-836379738 posted

    Has anyone figured out the issue with Excel only updating the 1st row? For example:

    UPDATE [Sheet1$B10:B10] SET F1=123
    UPDATE [Sheet1$B11:B11] SET F1=456
    UPDATE [Sheet1$B12:B12] SET F1=789

    This will update cell B1 three times and the final result is 789 in cell B1 even though I wanted to update B10, B11, B12.

    I have searched online and have seen the problem posted a handful of times but nobody has posted a resolution. It works fine locally and it works fine with an xls file but this problem happens with xlsx & xlsm files using the ACE.OLEDB.12.0 driver. I have reinstalled the drivers and rebooted the server a few times with no change in the result.

    Any help is very much appreciated!

    Mike


     

     

     

    Wednesday, July 13, 2011 1:44 PM