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

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.ActiveWorkboo开发者_如何学Gok.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)"

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",...

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