ERROR! (Using Excel Ranges with Range from C #)
Further I am trying to save a set of objects in an Excel sheet. Each time a function is called to store a value, it must allocate the next cell in the column A
to hold that object.
However, the exception is thrown by the Interop library on the first call 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 was thrown from a COM library, the built-in information is not very helpful. However, here it is:
"Exception from HRESULT: 0x800A03EC"
"\ r \ n Server stack trace: \ r \ n \ r \ n \ r \ nException thrown at [0]: \ r \ n in 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 in 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) 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 (MouseEve ntArgs mevent) \ r \ n at System.Windows.Forms.Control.WmMouseUp (Message & m, MouseButtons, 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) "Windows.Forms.Button.OnMouseUp (MouseEve ntArgs mevent) \ r \ n in System.Windows.Forms.Control.WmMouseUp (Message & m, MouseButtons, Int32 clicks) \ r \ n in 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) "Windows.Forms.Button.OnMouseUp (MouseEve ntArgs mevent) \ r \ n in System.Windows.Forms.Control.WmMouseUp (Message & m, MouseButtons, Int32 clicks) \ r \ n in 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) "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) "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) "
a source to share
I also fought.
The name I was trying to add was CEB04 ==> this refers to an existing excel (2007) cell far away in the righ columns. Since this is the real address excel does not allow it.
Just add a prefix before "MyAppNames_" + "CEB04" and you will be fine.
and by the way, using C # and .net4 you can avoid typing Type.Missing as it allows optional parameters.
This snippet works great:
var name= "MyAppNames_" + "CEB04";
var address = "=" + Constants.ValidationSheetName + "!" + target.Address;
worksheet.Names.Add(name, address,true);
a source to share