wolfgang-sap commented 1 month ago

When our add-in reads named items while an other Excel window is focused the type of the named items is null instead of range or string.

Your Environment

Excel on Windows Desktop

Expected behavior

named items should have their type as when the add-in's Excel window is focused

Current behavior

named item's type is null

Steps to reproduce

insert a named range in an empty workbook
trigger a delayed function that reads the named items
focus another Excel window

Link to live example(s)

Scriptlab that reads named items delayed:

name: Blank snippet (2) description: Create a new snippet from a blank template. host: EXCEL api_set: {} script: content: | $("#run").on("click", () => tryCatch(run));

async function run() {
    setTimeout(loadNamedItems, 5000)
}

async function loadNamedItems() {
  await Excel.run(async (context) => {
    const namedItems = context.workbook.names.load({name: true, type: true});
    await context.sync()
    console.log(namedItems.items) // type should not be null here!
  });

}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
    console.error(error);
  }
}

language: typescript template: content: |

language: html style: content: |- section.samples { margin-top: 20px; }

section.samples .ms-Button, section.setup .ms-Button {
    display: block;
    margin-bottom: 5px;
    margin-left: 20px;
    min-width: 80px;
}

language: css libraries: | https://appsforoffice.microsoft.com/lib/1/hosted/office.js @types/office-js

office-ui-fabric-core@11.1.0/dist/css/fabric.min.css office-ui-fabric-js@1.5.0/dist/css/fabric.components.min.css

core-js@2.4.1/client/core.min.js @types/core-js

jquery@3.1.1 @types/jquery@3.3.1

Provide additional details

when another (not Excel) App is focused, the errors didn't occur.

Context

Our scenario: We manage several tables with own data models. Each table is recognized by a named range. We have event handlers for sheet / range deletion. In that case we check if one of our named ranges got deleted and if yes we remove the corresponding data model, assuming that the user removed our named range. With this bug we don't find our named ranges and remove all our data models. This case happens not very often but is very bad for our customers as their complex workbooks are broken and hard to recreate.

Useful logs

guoms1 commented 1 month ago

Hi, @wolfgang-sap

Sorry, but I couldn't reproduce the issue. It looks like there's a problem with how the logs are being output, which might be why you're not seeing the content. Another possibility is that I had my focus on the search window instead of the specific window you mentioned.

That said, you can try using my modified code to reproduce my behavior, which outputs the name and type of the Named item. If you still run into issues, please record a video showing the exact reproduction steps so I can better understand the problem.

https://github.com/user-attachments/assets/92c1f0bd-f2d8-444e-a1a4-34834ac5421b

wolfgang-sap commented 1 month ago

Hi @guoms1, the issue seems only to occur if you focus a second Excel window. When you focus a different App, the namedItem.type is correct.

https://github.com/user-attachments/assets/0cd7099b-ed0c-44ce-8f71-039b92f1f292

guoms1 commented 1 month ago

Hi, @wolfgang-sap

Thank you so much for your help and patience.

With your support, I was able to reproduce the issue and have confirmed that it is indeed a real issue. I've already involved the relevant team for further investigation, and they are now looking into it (#9911075).

Best regards,

OfficeDev / office-js

namedItem.type is null when other Excel Window is focused #5504