Search code examples
c#excelspreadsheetgear

How can you set the scope for a defined name programmatically using SpreadsheetGear?


The support for working with defined names in SpreadsheetGear isn't as good as for other components of Excel.

Can this be done? If so, how?

When I copy in a sheet from another workbook, the scope is limited to that worksheet, and I'd like to be able to apply it to the whole workbook.


Solution

  • This is how you define a name with workbook scope

    SpreadsheetGear.IWorkbook workbook = workbookView.ActiveWorkbook;
    SpreadsheetGear.INames definedNames = workbook.Names;
    definedNames.Add(name, refTo, SpreadsheetGear.ReferenceStyle.A1);
    definedNames[name].Comment = "SomeComment";
    definedNames[name].Visible = true;
    

    "When I copy in a sheet from another workbook, the scope is limited to that worksheet"

    If I understand you correclty, you cannot do what you want and it is logically impossible. The defined names always should have 'workbook scope', that is, it is workbook.Names that holds the defined names information. Now based on this fact, if you copy a sheet from workbookA to workbookB, the sheet holds nothing about the defined names of that workbook (workbookA.Names), thus it can never hold their references.

    I hope this helps.