Add Named Ranges in Excel (C#)

Add-in Express™ Support Service
That's what is more important than anything else

Add Named Ranges in Excel (C#)
 
Nicolas Fuerer




Posts: 6
 Joined: 2014-06-26
Hello

Im trying to add named ranges in excel from C#. When I create a normal Excel-AddIn-Project in Visual Studio, I can do this with the following code: 


Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range range = ws.Range[ws.Cells[1,1], ws.Cells[4,4]];
ws.Names.Add("MyName", range);


How Can I do that in a add-in-express .COM AddIn? I tried so many ways, but i can't get the active Worksheet.
For Example, this doesn't work: 


Excel.Worksheet ws = ExcelApp.ActiveSheet;


Thanks in advance,
Nicolas
Posted 26 Jun, 2014 07:51:11 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Nicolas,

As per http://msdn.microsoft.com/en-us/library/office/ff822753%28v=office.15%29.aspx, the ActiveSheet property returns an Object, not Excel.Worksheet. This is confirmed by this page (Excel Interop reference): http://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel._application.activesheet%28v=office.15%29.aspx.

That is, you need to use this approach (a raw sketch): 


object sheet = ExcelApp.ActiveSheet;
if (sheet is Excel.Worksheet) {
    Excel.Worksheet wks = sheet as Excel.Worksheet;
    // do your stuff
}


Does this help?


Andrei Smolin
Add-in Express Team Leader
Posted 26 Jun, 2014 08:08:35 Top
Nicolas Fuerer




Posts: 6
 Joined: 2014-06-26
Thanks a lot! Now it works!
With the range i have to do it the same way, i guess!? 



object sheet = ExcelApp.ActiveSheet; 
if (sheet is Excel.Worksheet) {
  Excel.Worksheet wks = sheet as Excel.Worksheet;
  object selection = ExcelApp.Selection; 
  if (selection is Excel.Range) { 
    Excel.Range range = selection as Excel.Range; 
    wks.Names.Add("MyName", range);
  }
}
Posted 26 Jun, 2014 08:38:08 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
I've missed releasing COM objects. Here' how this should look like (a raw sketch): 

object sheet = ExcelApp.ActiveSheet;  
if (sheet is Excel.Worksheet) { 
  Excel.Worksheet wks = sheet as Excel.Worksheet; 
  object selection = ExcelApp.Selection;  
  if (selection is Excel.Range) {  
    Excel.Range range = selection as Excel.Range;  
    Excel.Names names = wks.Names;
    Excel.Name name = names.Add("MyName", range); 
    Marshal.ReleaseComObject(names); names = null;
    Marshal.ReleaseComObject(name); name = null;
  } 
  if (selection != null) Marshal.ReleaseComObject( selection); selection = null;
} 
if (sheet != null) Marshal.ReleaseComObject(sheet); sheet = null;



Andrei Smolin
Add-in Express Team Leader
Posted 26 Jun, 2014 09:00:37 Top