Excel is not opening Properly in Latest version Microsoft® Excel® for Microsoft 365 MSO (Version 2411 Build 16.0.18227.20082) 64-bit which is opening in earlier version

[Jayshiv1408Dev]

My Environment
Platform : PC desktop
Host: Excel
Office version number: Microsoft® Excel® for Microsoft 365 MSO (Version 2411 Build 16.0.18227.20082) 64-bit
Operating System: Windows 11 pro

Hi team,
I'm opening excel using base64 which is not opening properly due to some define name which is not present in excel but is showing me when I use method to get all define name.(this was not coming in older version of excel).
here is image of those define name which is not exists in name Manger but it's showing me when I try to retrieve define name using office-js. So after try to set it's visibility true those define names using this code it's throwing me an error.

const names = context.workbook.names; names.load(); context.load(workbook.names); await context.sync(); // Iterate over each named item for change visibilty for (let i = names.items.length - 1; i >= 0; i--) { workbook.names.items[i].visible = true; } await context.sync();

Error.zip

Excel file is opening using same code snippet previously in version Microsoft® Excel® for Microsoft 365 MSO (Version 2410 Build 16.0.18129.20158) 64-bit
After updating it's not opening.
My excel version after update is Microsoft® Excel® for Microsoft 365 MSO (Version 2411 Build 16.0.18227.20082) 64-bit

here is my code snippets for opening excel using base64.
Code.txt

Find outs/Observation (Might be helpful to identify an issue)
In this I find out in latest version of excel I'm getting some define name which is not present in excel so when try to set its visbilty = true those it's throwing exception.
Here is Error which
Error: {"name":"RichApi.Error","code":"InvalidArgument","traceMessages":[],"innerError":null,"debugInfo":{"code":"InvalidArgument","message":"The argument is invalid or missing or has an incorrect format.","errorLocation":"NamedItem.visible","statement":"name.visible = ...;","surroundingStatements":["var workbook = context.workbook;","var names = workbook.names;","var name = names.getItem(...);","// >>>>>","name.visible = ...;","// <<<<<","var name1 = names.getItem(...);","name1.visible = ...;","var name2 = names.getItem(...);","name2.visible = ...;"],"fullStatements":["Please enable config.extendedErrorLogging to see full statements."]},"httpStatusCode":400}
Might be my observation is wrong.
Why I have to remove named range and then set new named range visibility to true from excel?
Here is my older ticket for this reason I have to remove Named Range and then set its visibility before opening new excel..
NamedRange Courrption Ticket

[RuizhiSunMS]

HI @samantharamon, thx for reaching out here. I can repro it. Put it as #9610580 into our backlog. I'll contact our experts. Will reply here if any update.

[shermaro91]

Hi @RuizhiSunMS thanks to confirm this. But can you please share by when this issue will be resolved? As it's impacting our business and our clients continually calling us to resolve this asap.

[qinliuMSFT]

Hi @shermaro91, thank you for reporting this issue, I printed the workbook.names after inserting from base64, there is only 1 named item in the collection.

This named item appears to be invalid due to the unrecognized formula, which is why it has the type "Error" and the value #NAME?

Could you help me check what the named item looks like in the original workbook? Has it changed during the insert from base64 process? If it hasn't changed, will setting visible to true in the original workbook throw an error?

Or share the original workbook with us, which may help us further investigation. Thanks a lot.

[Jayshiv1408Dev]

Hi @RuizhiSunMS
Here is my source file
testFile101.xlsx

[qinliuMSFT]

Hi @Jayshiv1408Dev, thanks for your quick reply.

Could you please help me understand the meaning of "not opening properly"? Is the document unable to be opened(insert from base64 failed), or are some ranges inconsistent with the source file due to this invisible named item?

After reviewing your source file, it seems that the named item I mentioned in above comment is also not visible in the source file and cannot be set to be visible.

[Jayshiv1408Dev]

@RuizhiSunMS @qinliuMSFT As you can see After inserting bytes I have to change visibility of Define name (Already attached ticket why I have to do this.)That time it's thrown an exception. If I didn't change visibility of define Name then it cause problem(which also mention in my attached ticket.)
That's reason I write it's not opening properly.
Here again I'm attaching my previous ticket for reference NamedRange Courrption Ticket
Thanks.

[qinliuMSFT]

Hi @Jayshiv1408Dev , please check the namedItem type before set to visible.

for (let i = names.items.length - 1; i >= 0; i--) {
      if (workbook.names.items[i].type !== "Error") {
        workbook.names.items[i].visible = true;
      }
    }

The testFile101.xlsx you provided only have 1 namedItem on workbook scope, so please be note if you run the new sample script on this file, it will shows none in name manager when filter names scoped to workbook. It's as expected.

[Jayshiv1408Dev]

@qinliuMSFT Thanks for Solution.
For now, my problem is solved.
Can you please tell me the reason behind this? Because the same file is opening and then it is changing the visibility of named ranges without exception, but it is causing an issue in the latest version of Excel.

[qinliuMSFT]

Hi @Jayshiv1408Dev , the formula in this namedItem is =#NAME?, which is neither a valid formula nor a valid range address, so one of the internal function returns E_INVALIDARG.

Can you confirm that in previous versions, this namedItem has the same formula and has not changed? Why do you want to make such a namedItem visible?

[microsoft-github-policy-service]

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

[microsoft-github-policy-service]

This issue has been closed due to inactivity. Please comment if you still need assistance and we'll re-open the issue.