Accessing Excel cells by name instead of "coordinates" like A1

Question

I have an Excel Sheet where I've named the cells I have to fill from my code. The reason for the named fields is that the customer has the possibility to re-arrange the cells.

To make it a little clearer: I used the field shown below to name the cells.

My problem is now that I have no idea how to address these fields from C# using office interop.

Range r = Sheet.get_Range("M_Leitung", Type.Missing);

doesn't work. Since I don't have any ranges defined, just single cells where I have to insert the values, I'd need a function that returns the named cell.

Answer 1

I just simulated your scenario and the following code worked like a charm (where I labeled A1 in sheet2 as M_Leitung):

Edit: updated code - When you have multiple sheets, you need to refer the names at workbook scope which will return fully qualified address (and hence the resulting range knows which sheet to pick the address from)

private static void Main(string[] args)
{
    string FileName = Path.Combine(Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath), "Book2.xlsx");

    Application excelApp = new Application();
    Workbooks excelWorkbooks = excelApp.Workbooks;
    Workbook report = excelWorkbooks.Open(FileName, 0, false, 5, "", "", true, XlPlatform.xlWindows, "", true, false, 0, false, false, false);

    var y = report.Names.Item("M_Leitung").RefersToRange.Value;

    Console.WriteLine(y);
    excelWorkbooks.Close();
    excelApp.Quit();
}

Note that you do create a named range automatically when you rename a cell. If you created a range of say A1:B1 and you selected that. Excel would show you the named range in that corner label rather than the addresses which proves it works both ways.

HTH

Answer 2

Defined Names in Excel have scope, either Workbook or Sheet (ie Global or Local). Typing them in like you've done creates them as Workbook scope. Use the Name Manager to check scope.

Use the Names & RefersToRange properties, not Range to access them. The syntax is slightly different:

Workbook scope: Workbook.Names("M_Leitung").RefersToRange.Value

Worksheet scope: Workbook.Sheet.Names("M_Leitung").RefersToRange.Value

The reason for this is so you can create the same Name on different sheets.