Why created name range not able to get named item range?

Question

I tried to read range from named item in workbook, but getting an error:

This operation is not permitted for the current object.

First of all created name range(using add() method on names object).

Excel.run(function (ctx) {
    var sheet = ctx.workbook.names.add("MyRange", "Sheet1!A1B2");
    return ctx.sync().then(function () {
        console.log("range name added");
    }).catch(function (e) {        
        console.log("Error Message is -> " + e.message);
    })
});

Till now code works very fine. Now I want to read a range for an existing named range. So I made some changes to my code:

Excel.run(function (ctx) {
    var sheet = ctx.workbook.names.add("MyRange", "Sheet1!A1B2");
    return ctx.sync().then(function () {
        console.log("range name added");
        var range = ctx.workbook.names.getItem("MyRange").getRange();
        range.load("address");
        return ctx.sync().then(function () {
            console.log(range.address);
        });
    });
}).catch(function (e) {
    console.log("Error Message is -> " + e.message);
});

When I try to run this code I get the error above. I used same method as mentioned in Office.js API.

Kim Brandl · Accepted Answer · 2017-11-10T15:34:51.273

Seems like the syntax that you're using to create the named item (i.e., passing a string as the second parameter instead of passing a Range object) is making it so that the named item you're creating isn't actually a Range object. Therefore, when you subsequently try to treat that named item like a Range object, you're getting an error.

Here's a code snippet that creates a named item for a range and then gets that named item (a range) and writes its address to the console:

Excel.run(function (ctx) {

    // Create named item "MyRange" for the specified range.
    var sheet = ctx.workbook.worksheets.getItem("Sample");
    var myRange = sheet.getRange("A1:E1");
    sheet.names.add("MyRange", myRange);

    return ctx.sync()
        .then(function () {

            // Get the range for the named item "MyRange" and load its address property.
            var myNamedItem = sheet.names.getItem("MyRange");
            var range = myNamedItem.getRange();
            range.load("address");

            return ctx.sync()
                .then(function () {
                    console.log("Address of range: " + range.address);
                });
        });
});

You can quickly and easily try this code snippet yourself in Excel by using Script Lab (https://aka.ms/getscriptlab). Simply install the Script Lab add-in (free), then choose "Import" in the navigation menu, and use the following GIST URL: https://gist.github.com/kbrandl/89706cb9808bd7815eb0c89930ce526c.

Thanks Kim it worked now. but can you please explain me how your code is different from my code because instead of adding direct name on range i am adding name on range string. please can you explain. — Pavan Divekar, Nov 13 '17 at 05:27
If you want to create a named item for a Range, you must specify a **Range object** as the second parameter when you call the `NameItemCollection.add(...)` method. If you specify a string instead of a Range object, then Office JS does not interpret that string as a Range object, so when you subsequently call the `getRange()` method on that named item, an error will be thrown. (Because the `getRange()` method is not valid for strings.) — Kim Brandl, Nov 13 '17 at 17:20

Why created name range not able to get named item range?

1 Answers1