CODEWITHSUNDEEP
c#excel
Piper
Piper

Reputation: 55

How to update cells in excel?

I have an Excel sheet and wish to edit fields.

With this code I can edit information in the excel file.

This table starts at A1. It won't let me post pictures. So picture a 3 x 2 matrix. With City State and Zip as headers in A1, A2, and A3. Below them Detroit Michigan 48307 in B1, B2, and B3. Can't make it any clearer than this. 

{
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
            string sql = null;
            MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ace.OLEDB.12.0;Data Source=" + filePath1.Text + ";Extended Properties=\"Excel 8.0; HDR=Yes;\";");
            MyConnection.Open();
            myCommand.Connection = MyConnection;

            String mySheet = "Sheet1$";
            String mySET = "State = 'Michigan";
            String myWHERE = "Zip = '48102";
            sql = "UPDATE [" + mySheet + "$] SET " + mySET + "' WHERE " + myWHERE + "'"; 
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();
            MyConnection.Close();
        }

Now when I edit this excel file and put in block A1 some text and shift my 3 x 2 matrix down a couple rows.. It no longer works and won't update. I'm assuming that the code is defaulting to look for those header names in row 1,

IS there a way to tell the code to start searching at a particular row and column for the table of information?

Please do not post or click that this question is already answered or not clear.. This is as clear as I can make it, if it's not clear just ask a question. I've looked for answer to this and haven't found any.

Thank you.

Upvotes: 1

Views: 332

Answers (1)

Tim Williams
Tim Williams

Reputation: 166256

Try 

"UPDATE [" + mySheet + "$A4:C10] SET "...

(substitute the actual range you want to update)

Upvotes: 1

Related Questions