ERROR! (Using Excel's named ranges from C#)

Question

In the following, I am trying to persist a set of objects in an excel worksheet. Each time the function is called to store a value, it should allocate the next cell of the A column to store that object.

However, an exception is thrown by the Interop library on the first call to get_Range(). (right after the catch block)

Does anyone know what I am doing wrong?

private void AddName(string name, object value)
        {
            Excel.Worksheet jresheet;
            try
            {
                jresheet = (Excel.Worksheet)_app.ActiveWorkbook.Sheets["jreTemplates"];
            }
            catch
            {
                jresheet = (Excel.Worksheet)_app.ActiveWorkbook.Sheets.Add(Type.Missing, Type.Missing, 
                                                                       Type.Missing, Type.Missing);
                jresheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVeryHidden;
                jresheet.Name = "jreTemplates";
                jresheet.Names.Add("next", "A1", true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            Excel.Range cell = jresheet.get_Range("next", Type.Missing);
            cell.Value2 = value;
            string address = ((Excel.Name)cell.Name).Name;
            _app.ActiveWorkbook.Names.Add(name, address, false,
                                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                                      Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            cell = cell.get_Offset(1, 0);
            jresheet.Names.Add("next", ((Excel.Name)cell.Name).Name, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        }

Since the exception is thrown from a COM library, the embedded information does not seem very useful. However, here it is:

"Exception from HRESULT: 0x800A03EC"

"\r\nServer stack trace: \r\n\r\n\r\nException rethrown at [0]: \r\n at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)\r\n at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)\r\n at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2)\r\n at WorkbookTemplateManager.EditTemplateForm.AddName(String name, Object value) in C:\Documents and Settings\QueBITuser\My Documents\Visual Studio 2008\Projects\JRE.WCF\WorkbookTemplateManager\EditTemplateForm.cs:line 143\r\n at WorkbookTemplateManager.EditTemplateForm.SaveTemplate(Object sender, EventArgs args) in C:\Documents and Settings\QueBITuser\My Documents\Visual Studio 2008\Projects\JRE.WCF\WorkbookTemplateManager\EditTemplateForm.cs:line 124\r\n at System.Windows.Forms.Control.OnClick(EventArgs e)\r\n at System.Windows.Forms.Button.OnClick(EventArgs e)\r\n at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)\r\n at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)\r\n at System.Windows.Forms.Control.WndProc(Message& m)\r\n at System.Windows.Forms.ButtonBase.WndProc(Message& m)\r\n at System.Windows.Forms.Button.WndProc(Message& m)\r\n at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)\r\n at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)\r\n at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)"

Just a wild guess: did you try jresheet.Names.Add("next", "=jreTemplates!$A$1",... ? — Richard Morgan
– Richard Morgan, Commented May 19, 2010 at 14:04
I don't understand the reason for having the '=' on the front, but it seems to work. If you suggest this as an answer, I will approve it. — mcoolbeth
– mcoolbeth, Commented May 19, 2010 at 16:11
Glad that worked for you; since I didn't test it, I was hesitant to make it answer. :) Thanks. — Richard Morgan
– Richard Morgan, Commented May 19, 2010 at 17:34

Richard Morgan · Accepted Answer · 2010-05-19 17:34:17Z

1

From some looking around, I found some examples where the string itself has an equals and an explicit reference on the Names.Add method. Something like:

 jresheet.Names.Add("next", "=jreTemplates!$A$1",...

answered May 19, 2010 at 17:34

Richard Morgan

7,6799 gold badges51 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

codea · Accepted Answer · 2013-03-21 14:29:20Z

0

I was struggling too.

The name I was attempting to add was CEB04 ==> this refers to an existing excel (2007) cell far away in righ columns. So since it is a real address excel does not allows it.

Just add a prefix before like "MyAppNames_" + "CEB04" and it will be fine.

and by the way using c# and .net4 you can avoid typing the Type.Missing since it allows optional parameters.

This snippet works fine:

var name= "MyAppNames_" + "CEB04";
var address = "=" + Constants.ValidationSheetName + "!" + target.Address;
worksheet.Names.Add(name, address,true);

edited Mar 21, 2013 at 14:29

answered Mar 21, 2013 at 14:23

codea

1,6112 gold badges18 silver badges34 bronze badges

Collectives™ on Stack Overflow

ERROR! (Using Excel's named ranges from C#)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related