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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With