NamedRanges in Excel 2007 with VSTO SE

Visual Studio Tools for Office

spano

When developing a document-customization addin for Excel 2003 with VSTO, adding a named range to a worksheets results in a Microsoft.Office.Tools.Excel.NamedRange inserted in the Sheet1.Designer.cs wich I can then reference from my code.

The question is: when developing an application-level addin for Office 2007 with VSTO SE (as no document-customization project is available), how can I obtain a named range from code

Suppose I create a range in the sheet from Excel by selecting a couple of cells and clicking the "Name a range..." item in the context menu. How can I then reference that range from code Is it possible to get a reference to a Microsoft.Office.Tools.Excel.NamedRange as it was in 2003


Thanks in advance,

Sole


Top

Re: Visual Studio Tools for Office NamedRanges in Excel 2007 with VSTO SE

Cindy Meister

You need a slightly different syntax with C# and "plain interop" :-)

Something along the lines of Excel.Range barRange = oWS.get_Range("ChartData", missing);





Top

Re: Visual Studio Tools for Office NamedRanges in Excel 2007 with VSTO SE

Dennis Wallentin

Hi Sole,

Let see if the following can get You started in the right direction:

Code Snippet

Friend Sub Create_Name_Range()

Dim xlsheet As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet

Dim rnArea As Excel.Range = xlsheet.Range("A1:A3")

Dim Name As Excel.Name = Globals.ThisAddIn.Application.ActiveWorkbook.Names.Add("XLDennis", rnArea)

MessageBox.Show(Name.RefersTo.ToString)

End Sub

Friend Sub Use_Name_Range()

Dim xlRange As Excel.Range = Globals.ThisAddIn.Application.ActiveSheet.Range("XLDennis")

MessageBox.Show(xlRange.Address.ToString)

End Sub





Top

Re: Visual Studio Tools for Office NamedRanges in Excel 2007 with VSTO SE

spano

Thank you both!

Here is the C# code in case it is helpful for someone else:

Code Snippet


private Worksheet _sheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;

//create a named range
Range myRg = _sheet.get_Range("A1", "C3");
Name myName = _sheet.Names.Add("MyNameRange",

myRg,true,Type.Missing,Type.Missing,

Type.Missing,Type.Missing,Type.Missing,

Type.Missing,Type.Missing,Type.Missing);
MessageBox.Show(myName.Name+": "+myName.RefersTo.ToString());

//enumerate sheet names
string names = "There are "+_sheet.Names.Count+ " names: ";
IEnumerator en = _sheet.Names.GetEnumerator();
while (en.MoveNext()){
object nameObj = en.Current;
Name name = nameObj as Name;
if (name != null)
names += name.Name;
}
MessageBox.Show(names);

//get a range for a given name
Name rgName = _sheet.Names.Item("MyNameRange",

Type.Missing, Type.Missing);
Range hr = rgName.RefersToRange;
hr.Value2 = 1;

//another way of getting the named range
hr = _sheet.get_Range("MyNameRange", Type.Missing);
hr.Value2 = 2;







Top

Re: Visual Studio Tools for Office NamedRanges in Excel 2007 with VSTO SE

Dennis Wallentin

Thanks Sole for coming back to us and show how it's achieved with C#

I hope I will be able next time to give it a try with C#





Top