CODEWITHSUNDEEP
c#excelinterop
Eddie
Eddie

Reputation: 700

Updated Excel Cell value by Name Reference in C#

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'

Upvotes: 3

Views: 4181

Answers (3)

Arjan
Arjan

Reputation: 17054

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.

Upvotes: 0

InBetween
InBetween

Reputation: 32750

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";

Upvotes: 1

Francesco Baruchelli
Francesco Baruchelli

Reputation: 7468

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";

Upvotes: 2

Related Questions