Update specific cell of excel file using oledb

Question

I want to update the specific cell of the excel file using oledb. Say I7 cell, for some reason always I1 is getting updated. Can anyone tell me what's wrong with this code?

OleDbConnection oledbConn = new OleDbConnection(connString);
oledbConn.Open();

// I want to set the value of I7 cell to 22, for some reason value is I1 is getting updated :(
OleDbCommand cmd = new OleDbCommand("UPDATE [" + sheetName + "$I7:I7] SET F1=22", oledbConn);
int result = cmd.ExecuteNonQuery();
Console.WriteLine(result);
oledbConn.Close();

Does your excel have filters? – jacob aloysious Apr 09 '13 at 06:22 — jacob aloysious, Apr 09 '13 at 06:22
Thanks for your response, No filters is set. – Atul Sureka Apr 09 '13 at 06:24 — Atul Sureka, Apr 09 '13 at 06:24

score 1 · Answer 1 · answered Apr 09 '13 at 06:26

1

This may purely be an issue with the cell address scheme you're using. Try $I$7:$I$7 intsead. Also, what is that F1 thing in the query?

answered Apr 09 '13 at 06:26

dotNET

33,414
24
162
251

Getting this error now - The Microsoft Office Access database engine could not find the object 'Sheet1$I$7:$I$7'. Make sure the object exists and that you spell its name and the path name correctly. F1 is the cell number in the selected range – Atul Sureka Apr 09 '13 at 06:33
So are you updating I7 or F1? – dotNET Apr 09 '13 at 06:36
I don't think we need to use ! mark after sheet name, I checked, even with ! it does not work. Here is the update query "UPDATE [Sheet1!$I$7:$I$7] SET F1=22" – Atul Sureka Apr 09 '13 at 07:08
You're confusing the two cells. If you're updating I7, then what is `SET F1=22` doing? It should rather be like `UPDATE [Sheet1] SET I7=22`. Haven't checked the syntax, but I don't see a role for F1 here. – dotNET Apr 09 '13 at 07:16
No above query does not work. You can check this link for syntax http://forums.asp.net/t/1214491.aspx/1 – Atul Sureka Apr 09 '13 at 07:29

score 0 · Accepted Answer · answered Apr 12 '13 at 09:54

0

I ran the same program on excel 2007, it worked smoothly. For excel 2013 it does not work. Seems like a bug in excel 2013.

answered Apr 12 '13 at 09:54

Atul Sureka

3,085
7
39
64

score 0 · Answer 3 · answered Aug 31 '23 at 20:31

i read this blog how Atul Sureka said forums.asp.net/t/1214491.aspx/1 " 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. "

Update specific cell of excel file using oledb

3 Answers3

Linked