CODEWITHSUNDEEP
exceloffice-jsexcel-addinsoffice-scripts
jkpieterse
jkpieterse

Reputation: 2956

Problem with names collection of worksheet object

I have encountered a problem with OfficeScript (I think).

I wrote this script:

async function main(context: Excel.RequestContext) {
  var RowCt = 0;
  var sheetName = "";
  let workbook = context.workbook;
  let worksheets = workbook.worksheets;
   let MyNames = workbook.names;
   MyNames.load("name, formula, visible");
  let selectedSheet = worksheets.getItemOrNullObject("Names list");
  await context.sync();
   if (selectedSheet.isNullObject) {
     selectedSheet = workbook.worksheets.add("Names list");
   }
   await context.sync();
//This seems to work but omits names scoped to the worksheet
   for (let i = 0; i < MyNames.items.length; i++) {
     RowCt++
     selectedSheet.getCell(RowCt, 0).getResizedRange(0, 3).values = [
       ["Workbook", MyNames.items[i].name, "'" + MyNames.items[i].formula, "'" + MyNames.items[i].visible]
     ];
   }
   worksheets = workbook.worksheets;
   worksheets.load("items/name");
   await context.sync();
//This appears to work, but wrongfully includes ALL names scoped to the workbook:
   for (let i in worksheets.items) {
     sheetName = worksheets.items[i].name;
     await context.sync();
//If I omit this sheetName test, it loops through all worksheets (expected) and lists all 
//workbook-level names as many times as there are worksheets (unexpected)
     if (sheetName == "Sheet1") {
       var myNames = worksheets.items[i].names;
       await context.sync();
       myNames.load("name, formula, visible");
       await context.sync();
       //console.log(myNames.items[0].name);
       for (let j in myNames.items) {
         RowCt++;
         selectedSheet.getCell(RowCt, 0).getResizedRange(0, 3).values = [
           [sheetName, myNames.items[j].name, "'" + myNames.items[j].formula, "'" + myNames.items[j].visible]
         ];
       }
     }
   }
}

If I run this against a workbook which has both workbook-level range names and worksheet-level range names, I get all of the workbook-level range names twice. Once for the global list (first loop through the names) and once for the "locally defined" names (look for the loop through all worksheet tabs where I only work through the names of Sheet1). The loop that lists the globally defined names omits the names scoped to worksheet 'Sheet1', but if I enumerate the names collection of Sheet1, I get the locally defined names, and I get all global names too. This is WRONG.

Upvotes: 4

Views: 191

Answers (1)

Raymond Lu
Raymond Lu

Reputation: 2236

Thanks for your patient, it seems you have talked 2 issues:

  • The names collection of the workbook object includes only workbook-scoped names, EXCLUDING all worksheet-scoped names.
  • The names collection of the worksheet includes names scoped to the worksheet AND ALL WORKBOOK-LEVEL NAMES.

For 2#, I repro'ed this issue, and a bug(#3983983) was created to track this issue. Team is investigating on this issue.

For the 1#, our design was to seperate workbook and worksheets, but we will revisit the design, we will let you know soon

Upvotes: 1

Related Questions