Updated Excel Cell value by Name Reference in C#

Question

I have a named cell in excel lets say its called "myCell" In C# I wish to be able to access this cell and update its value. I've tried

Globals.ThisAddIn.Application.Names.Item(@"myCell").Value = "myValue";

But this throws a COM exception with the message 'Exception from HRESULT: 0x800A03EC'

score 2 · Accepted Answer · answered Sep 08 '11 at 10:21

Your problem is that:

Globals.ThisAddIn.Application.Names.Item(@"myCell")

does not return a Range for which you can set the value, but an object of type Name. You can get the Range representing the cell you are looking for using the RefersToRange property and then set the value for this object. This all translates into something like this:

Globals.ThisAddIn.Application.Names.Item(@"myCell").RefersToRange.Value = "myValue";

InBetween · Answer 2 · 2011-09-09T11:43:27.760

1

Just use the Range object. A named cell is a named Range, so this should work:

myWorkBook.myWorkSheet.Range["myCell"].Value = "myValue";

P.D. I have no way to check this and I'm not sure if you can assign the value directly to a Rangeobject or you need to specify the offset of the cell inside the Range (as it can contain many cells).

You can also try: (I dont remember if named ranges work directly with cells)

myWorkBook.myWorkSheet.Cells["myCell"].Value = "myValue";

edited Sep 09 '11 at 11:43

answered Sep 08 '11 at 09:47

InBetween

32,319
3
50
90

Both give the message Non-invocable member 'Microsoft.Office.Interop.Excel._Application.Range' cannot be used like a method. – Eddie Sep 08 '11 at 09:52
1

@Eddie: Oops, change those `(` with `[`. Editted my post. – InBetween Sep 09 '11 at 11:42
Still triggers the same message with [] – William Walseth Mar 25 '14 at 20:35

score 0 · Answer 3 · edited May 23 '17 at 12:05

0

Use:

var cell = myWorksheet.Evaluate("defined_name");
if (cell != null) { cell.Value = "hello"; }

Or:

myWorksheet.Cells.SetValue("defined_name", "hello");

In the last case, trying to set a value for a non-existing name gives "Exception from HRESULT: 0x800A03EC"

If checking for null doesn't work, check this answer for a list of int values as errors.

edited May 23 '17 at 12:05

Community

1
1

answered Nov 18 '14 at 10:24

Arjan

16,210
5
30
40

Updated Excel Cell value by Name Reference in C#

3 Answers3