search

OfficeDev / office-js

A repo and NPM package for Office.js, corresponding to a copy of what gets published to the official "evergreen" Office.js CDN, at https://appsforoffice.microsoft.com/lib/1/hosted/office.js.
https://learn.microsoft.com/javascript/api/overview
Other
670 stars 96 forks source link

Adding COM add-ins breaks Office add-in's custom functions #2568

Closed toni-lyttinen closed 12 months ago

toni-lyttinen commented 2 years ago

If you have a workbook that has an Office add-in loaded and you add a COM add-in, the Office add-in's custom functions disappear and you receive the notification "One or more add-ins failed to install or load custom functions". If you have called the Office add-ins custom functions in cells and you add the COM add-in, the function calls "collapse" and a _xldudf_ namespace is added to all function calls. This is highly inconvenient as you have to import the Office add-in again in order to fix the workbook.

Your Environment

Expected behavior

When you add a COM add-in, the Office add-ins custom functions should remain instead of removing them from the instance. COM add-ins and Office add-ins should not conflict.

Current behavior

When you add a COM add-in, the Office add-ins custom functions are removed and user receives a notification "One or more add-ins failed to install or load custom functions". In addition to this, the cells containing Office add-ins custom function calls break into _xldudf_. The functions occasionally break aswell when you open multiple workbooks etc. I suspect this is related to the issue.

Steps to reproduce

  1. Generate office addin with yo office (Typescript, Excel, React)
  2. Add generated custom function to Excel instance
  3. Call custom function (e.g. Clock)
  4. Add a COM add-in (e.g. Analysis Toolpak)

Link to live example(s)

Provide additional details

Context

We are developing a Office add-in. The developed Office add-in will be used in parallel with a COM add-in. The add-ins should not conflict. Both add-ins should work when used in parallel

Useful logs

The last line of the logs occurs after you import a COM add-in. The screenshot below demonstrates how the cells "break" when you add a COM add-in.

JHJ-MS commented 2 years ago

Thanks for reporting @toni-lyttinen. It's a known issue and we have created an internal work item 6040488 on it. We will keep track of this issue and let you know if there are any updates.

toni-lyttinen commented 2 years ago

Thanks for reporting @toni-lyttinen. It's a known issue and we have created an internal work item 6040488 on it. We will keep track of this issue and let you know if there are any updates.

Thank you for replying. Hope you can solve this issue with ease. Looking forward to updates!

toni-lyttinen commented 2 years ago

Any progress on the internal item?

wh1t3cAt1k commented 2 years ago

@JHJ-MS just interested what would be the fixing strategy for this work item and what is the root cause?

Background: I was experiencing the same when I was experimenting with the removal of EquivalentAddins mechanism.

we have a COM-based addin and the modern add-in with the same function names. I felt this "EquivalentAddins" mechanism was not mainstream-tested and it was the source of obscure #NAME related bugs in our add-in (we have reports of the add-in being flaky this way but haven't been able to track this down properly).

So what I did was:

image

Thinking aloud, I do think this whole "conversion to XLL-compatible custom functions" only adds to the confusion, this mechanism feels unnecessarily complex and counter-intuitive to both the user and the developer.

I would expect the functions to just be compatible on the name basis, and for Excel to warn / if there are any conflicts between the different installed addins at runtime.

Namespacing should be solution enough for most users (however we are using an empty namespace in our add-in because we need compatibility with the old add-in).

4tti commented 1 year ago

This issue is real blocker for many our end-users. We cannot endlessly convince them to wait for the fix... the workarounds (either (re)inserting the office.js addin or keeping out of com addins, etc.) are not maintainable for long period of time! And we are waiting for the fix for months.

I understand the fix may require some major effort (because of some core design flaws) but the communication should be then really detailed and careful as this issue is basically endangering our success and makes it obvious that leveraging of custom functions feature was big mistake (in case it won't be fixed any time soon) especially as this issue is happening with MS provided com addins (e.g. Analysis ToolPak) :(

@keyur32, @jakobpn FYI

wh1t3cAt1k commented 1 year ago

Thanks so much for your post @4tti. I will go on a tangential here, but I am outlining a big picture that I'm seeing and that requires serious action on Microsoft's end.

We at Velixo have been working with Office.js for Excel for 2.5 years now and the issues around framework's quality were so numerous, and fixes taking so long that I am second-guessing myself whether it was the right choice for us to bet on this cross-platform technology long term. I sometimes don't feel comfortable advertising the new cross-platform product in large part because it feels flaky, and that is in some part due to all the outstanding framework troubles.

This issue is two-years old and was closed as "cannot reproduce" despite numerous reports from users.

I literally just received it again and it leads to the file not showing any UI updates while all functions display as #BUSY:

image

I mean, we should look at the sheer number of issues in this repository. I created a ticket about a file completely crashing Microsoft Excel two months ago, no resolution.

I definitely think Microsoft's product team needs to go top priority on making this framework production-ready at scale, dedicate more resources on improving the product quality and maybe put a pause on additional bells and whistles.

I personally would always be happy to assist in dedicated troubleshooting sessions together with the framework team and anything else we need to make this work.

@keyur32 @jakobpn @JinghuiMS I hope our cries reach you, and please don't consider this post a sign that I don't appreciate all that you guys did so far. This framework is a gem, we need to polish it!

@gmichaud @sshukurov I would also welcome your input here.

gmichaud commented 1 year ago

We are betting our future on office.js. At this point, only our Mac users are depending on it (we're not promoting it to our Windows users). The sheer number of issues and problems we have to go through in comparison to our COM-based add-in that litterally has 1000x more active users has me really worried. Our customers have come to depend and rely on our product, and we want to make sure we can keep customer satisfaction as high as it is right now when we finally start pushing users to the Office.js-based version of our product.

We do see huge potential in Office.js and are thrilled by the collaboration we're seeing with Microsoft no GitHub. I do love this cooperation as highlighted by @4tti I am a really hoping to see a clear way forward soon!

4tti commented 1 year ago

Unfortunately I must somewhat agree with @wh1t3cAt1k As of now I see some really critical things which are becoming more and more visible and are more and more considered as a blockers by our customers.

We do see huge potential in Office.js, especially with extremely changed approach by the Extensibility/Core teams when we are really listened and our ideas are quite often turned into real features and improvements. I love this cooperation and we are able to convince our customers to have similar passion around that.

However, some issues are making this passion to simply disappear (not on our end but unfortunately on our customer's side). Just to pick few:

  1. Not well designed deployment and 'same addin' identification (see https://github.com/OfficeDev/office-js/issues/2878), e.g. not covering at all possibility to switch between test/prod environments. Sidenote: I think the main reason/issue here is the manifest design when add-in creator MUST statically reference files for Custom Functions, Ribbon, etc. Would we have capability to register these things "just in time" it would not probably cause so much troubles; and moreover it would allow us to have dynamic behavior (e.g. for certain users we would have different set of Custom Functions and Ribbon Buttons than for other users).
  2. Custom Functions performance once we use large workbooks (many sheets with lot of formulas, especially while nested). This is being improved with the time which is really awesome! Still some things could have been improved - like e.g. have 'batching' of functions supported NATIVELY inside excel not forcing every consumer to do that on their side (it could be controlled by flag similarly as volatility).
  3. Undo stack being cleared (https://github.com/OfficeDev/office-js/issues/2802, https://github.com/OfficeDev/office-js/issues/2543, https://github.com/OfficeDev/office-js/issues/3317)

I am developer at heart so I understand some things CANNOT be easily implemented/fixed especially with such huge applications as Office Apps are.. but then clear and transparent communication is crucial. Not only because of us as adopters but also because of our customers. I've learned that sharing details and giving explanation often makes customers more understanding and patient.

Wenjun-Gong commented 1 year ago

Hi @wh1t3cAt1k , @4tti , @gmichaud and @toni-lyttinen , really appreciate for sharing the feedback with us. We did hear your voice. For the xldudf namespace issue, we have been actively looking into the root reasons and possible solutions. But due to the complexity, we are not able to share any ETA at this moment.

For the rest of the issues talked in this thread, seems they have separate GH tracks. Let's follow up in those separate threads. Please do create new issue if there is any one missed.

4tti commented 1 year ago

Hello @Wenjun-Gong, is there any update? It has been almost 1 month again. It would be extremely appreciated to have somehow more active sharing of the information.

This issue is really blocker for many customers!

eyalmolad commented 1 year ago

A real blocker for us as well. @Wenjun-Gong

donlvMSFT commented 1 year ago

Hi @4tti and @eyalmolad , thanks for the continuedly working, we're currently actively working on this xldudf issue, actually the scenario that when adding an Excel add-in after Custom Function is registered. We are still in progress on it and will share the info here once we get any fix. If your scenario is not like this, please let us know as it might be separate issue.

4tti commented 1 year ago

Hello @donlvMSFT , thanks for the info. Do you have any rough estimate of ETA? Something like H2 2023 or so. It would really help cause we are constantly asked by our customers.

Thanks again!

wh1t3cAt1k commented 1 year ago

@donlvMSFT will the "XLL equivalent add-in" switch be necessary after you deliver your fix, or will it be deprecated?

donlvMSFT commented 1 year ago

@4tti , for the scenario I mentioned, if you are in Office Insider program, or Office version is >15831.20122, I think you could have a try now. @wh1t3cAt1k , this fix won't change the "XLL equivalent add-in" switch.

eyalmolad commented 1 year ago

Our scenario is following:

Current situation:

  1. MAC user saves the file with UDF functions -> other user opens the file in Windows with the C# Addin. #NAME appears for all UDFs + a weird message that the compatible Addin can not be found. The only hack for now is to do a find/replace for all UDFs which is not acceptable for large files. Simply takes too long.
  2. MAC user opens the file saved by C# Addin. Same thing.

We do have a full control of the sources of both Addins, so we could change both codes bases in order to get the compatibility wanted. Thanks

4tti commented 1 year ago

@4tti , for the scenario I mentioned, if you are in Office Insider program, or Office version is >15831.20122, I think you could have a try now. @wh1t3cAt1k , this fix won't change the "XLL equivalent add-in" switch.

Hello, I can confirm we cannot reproduce locally. I'll let all the customers know and will get back to you. Good stuff!!! Thanks a lot.

taralex77 commented 1 year ago

Has this been fixed? We have a customer using our Excel add-in with custom function who consistently gets the custom formulas (namespace.formula()) replaced with @'C:/Users//AppData/Roaming/Microsoft/AddIns/VB ADDIN.xla'!_xldudf_NAMESPACE_FORMULA

I'm not sure what this "VB ADDIN.xla" is, but it's definitely not a part of our addin.

The only way to fix all the formulas is to reinstall the add-in. So the user is forced to reinstall the add-in multiple times a day in order to fix their custom formulas. This can't be normal.

donlvMSFT commented 1 year ago

@taralex77 , could you check the Office version? If Office version is >15831.20122, it should include the fix I mentioned before. If the issue still persist on latest Office version, there might be another issue, could you share with me a sample file for further investigation?

taralex77 commented 1 year ago

Thank you for getting back! The office version is 15831.20208 This happens at a customer's machine, I was not able to reproduce it locally. Is there anything that can be present in the machine (like a saved xlam file) that can mess with the add-in built-in function?

Best regards, Alex Tarasevich.

On Sun, Jan 8, 2023 at 9:36 PM donlvMSFT @.***> wrote:

@taralex77 https://github.com/taralex77 , could you check the Office version? If Office version is >15831.20122, it should include the fix I mentioned before. If the issue still persist on latest Office version, there might be another issue, could you share with me a sample file for further investigation?

— Reply to this email directly, view it on GitHub https://github.com/OfficeDev/office-js/issues/2568#issuecomment-1375127661, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABYJRFLKHHT5TSSK6OMT5RTWROPVXANCNFSM5VEHW7FQ . You are receiving this because you were mentioned.Message ID: @.***>

donlvMSFT commented 1 year ago

Hi @taralex77 , currently we know the bug as when add the .xlam/.xla add-in after CF is registered, the CF registration might be messed up. User could check the Developer->Excel Add-ins, to check whether there's any unused VBA add-ins been added, Developer tab is not shown by default, user could get that tab from File->Options->Customize Ribbon.

caolgood commented 1 year ago

I can reproduce the same issue @taralex77 described, using the setup in the OP

Initially it works ok, but after saving and reopening, the function is stuck on #BUSY, and the info bar on the bottom of the screen is stuck on "We're starting the add-ins runtime, just a moment..."

Then if the file is opened again with the toolpak addin disabled, the custom function cells are stuck in this state: ='C:\Program Files\Microsoft Office\root\Office16\LIBRARY\Analysis\FUNCRES.XLAM'!_xldudf_CONTOSO_CLOCK()

My Excel version is 16026.20214 sample file Book1 (6).xlsx

PS After a bit more testing, adding COM add-ins doesn't seem to cause this, it was only the .xlam/.xll "Excel add-ins". Although my sample size is small, I'm just testing the add-ins that came with my excel install

4tti commented 1 year ago

I can confirm that I saw this again in customer environment + some other weird behavior with the VBA (possibly COM) addins (in this case it was Analysis ToolPak) -> e.g. sometimes events triggering/handling is weird causing unexpected issues. When the add-ins were removed (disabled and links removed) everything worked fine.

donlvMSFT commented 1 year ago

Hi @4tti , we have another fix on the deploy road, which should be after build version 16.0.16227.20150. Could you take a try?

caolgood commented 1 year ago

@donlvMSFT I moved to the current channel (16.0.16227.20202) and I'm no longer able to reproduce any issues with XLL's. Thank you!

4tti commented 1 year ago

@donlvMSFT let us check!

Sorry for late reply!

4tti commented 1 year ago

Seems fixed! Thanks a lot.

4tti commented 1 year ago

Hello @donlvMSFT,

unfortunately this issues seems to be still present on some environments. Does it need certain windows/webview versions or is really office version crucial? Because today I had a call with one of the customers and the issue is reproducible on his end: image

Reinserting add-in fixed it. Excel: image

Active add-ins: image

So either the fix is not still covering all cases or there is regression.

macabacus-rm commented 1 year ago

Hello @donlvMSFT,

unfortunately this issues seems to be still present on some environments. Does it need certain windows/webview versions or is really office version crucial? Because today I had a call with one of the customers and the issue is reproducible on his end: image

Reinserting add-in fixed it. Excel: image

Active add-ins: image

So either the fix is not still covering all cases or there is regression.

We're seeing the same from one of our customers. The issue does not appear resolved (anymore).

parched commented 1 year ago

When I unzipped the xlsx and looked in sheetX.xml I found this had caused _xldudf_XXXX to be replaced with [2]!_xldudf_XXX. So, my guess is the fix stops the issue from occurring in the first place, but if you had it happen in a workbook and saved it, then it's permanently broken. I manually fixed it by running this on the unzipped xlsx, then zipping it up again.

grep -PRl '\[2\]\!_xldudf' | xargs sed -i -e 's/\[2\]\!_xldudf/_xldudf/g'
4tti commented 1 year ago

@parched thanks for the info! That's definitely very helpful. However, imagine customer scenario when they have thousands of reports ... this should be fixed by MS.

donlvMSFT commented 1 year ago

Hi, the fix would take effect after you re-install the add-in, or install in a new document, or other ways triggering the custom function register.

4tti commented 1 year ago

oh, ok - that's pretty important information. Thanks @donlvMSFT Let us check.

akshay70 commented 1 year ago

@donlvMSFT thanks for your work on this!

Two questions on the current solution:

  1. Is there a more automatic fix that will be coming in the future that doesn't require manually reloading the addin?
  2. Is there any shortcut or way to program in a shortcut that reloads the addin when pressed?

Mainly the fix has a lot of friction with clients who don't understand what the issue or fix is. So I'm looking if there's a better or more automatic way to fix the issue for them.

ilex-sgangwani commented 1 year ago

@donlvMSFT - We're still having this issue and we have created our add-in just two months back. Currently on Office version 2306 Build 16.0.16529.20100. The internal company add-in is the office js one and we have COM and XLA add-in from BBG. Every time we make a change to our office.js add-in, the formulas change back to having xldudf appended to the start. What is the potential solution here as this is become very annoying for the users.

ilex-sgangwani commented 1 year ago

@donlvMSFT - We continue to have this issue every time we release a new version of the add-in (manifest version change + other changes). What is the best way to prevent this from happening? Or a workaround to quickly resolve the issue when it happens?

donlvMSFT commented 12 months ago

Hi @ilex-sgangwani ,

Thanks for reaching out, seems it's another scenario compared with the origin one. Could you please raise another issue with detail info and repro steps, that would help us a lot to identify the root cause.

Thanks a lot.

4tti commented 11 months ago

Really? So I keep telling our customers, that they need just to reinsert the add-in and save it to have it fixed once for all.... and it's once for all till the next release which requires manifest update.... 😞

@ilex-sgangwani have you created new ticket? If so can you link it please?

ilex-sgangwani commented 11 months ago

@4tti - I had created a separate ticket which was a potential solution for the root problem of formulas getting changed to have "xldudf" appended at the start. The solution I found was that the formula updates once the add-in loads. That said, the add-in doesn't load automatically unless you open the Task Pane or trigger the onload. The issue I raised below was to set the startup behaviour to load to resolve the above issue but that resulted in a separate issue. Thread below for reference.

https://github.com/OfficeDev/office-js/issues/3558#top

4tti commented 10 months ago

Can we reopen this? Our customers are still facing this issue... @donlvMSFT, @adrianwu8516, @JHJ-MS

donlvMSFT commented 10 months ago

@4tti , as mixed things are including in this long thread, and the original one in this thread is already resolved, I'd like to suggest you please create a new issue with your details, please link to this if needed.

4tti commented 10 months ago

@donlvMSFT The details are the same, same scenarios - simply COM add-ins are causing issues with Custom Functions and the only solution is to reinsert add-in.

eyalmolad commented 10 months ago

Same issue here. Some of our customers have SaveAsAdobePDF installed on their machines. All UDF are getting SaveAsAdobePDF! prefix that simply breaks the file.

4tti commented 9 months ago

Heh and I found out there is already similar issue reported here https://github.com/OfficeDev/office-js/issues/2051

This is serious ISSUE, hitting many customers, it was reported like 3 or 4 times in separate threads and still not resolved for years!!!

@keyur32, @jakobpn @adrianwu8516 @shaofengzhu @Wenjun-Gong @jeremy-msft , @sumurthy ... please give this kind of issue priority. Every single customer is being impacted whenever they use COM/XLA addins... :(

sk1136 commented 9 months ago

Hi @4tti assuming you are having similar issues as us. Let us know if you are able to solve or hear back from Microsoft. Thank you.

donlvMSFT commented 9 months ago

Hi there,

Thank you for callout, we're continuedly looking into these _xldudf_ issue, as we mentioned before, this behavior reflects to several root causes and it's complicated causing much effort. Thanks for your patience.

Currently we identified another root cause and working on the improvement, hopefully the improvement would be on Insider for you validation next month. We'll keep update the progress in the issue #2051 .