Remove AddIn path from UDF in Excel formula

My addin was xla, now I use excelDNA, so it becomes xll, When I open spreadsheet built in previous version of My addin, for the UDF, it shows myUDF with path of xla. e.g "C:\Program Files\Installation folder\MyUDFs.xla!MyUDF", when I click Edit link and change source to "C:...\MyUDFs.xll" I got a pop up which says "Excel cannot update one or more links in this workbook. To update the links, open all the link source files(click Edit Links on the Data tab). To be sure all calculations are updated. press F9" I click OK, then the path of MyUDF changes from xla to xll, e.g. C:\Program Files\Installation folder\MyUDFs.xll!MyUDF For clients, this will break all their spreadsheets (could be 100+) built in previous version. I know I can write a VBA code to remove paths from all MyUDF. but it is not ideal since users have to open up spreadsheet and put the code in开发者_开发百科 spreadsheet and run.

I wonder if there is a better/more convenient way for clients to solve the issue thanks

Internally, Excel stores different information for an .xla function and an .xll function. It's not so easy to around so that you can make an .xll that is compatible with functions that were entered into the sheet as functions in an .xla.

You can also see how Excel stores this information by poking around inside the .xmlx file a bit.

This Wilmott discussion might be relevant: http://www.wilmott.com/messageview.cfm?catid=10&threadid=79763 For your case the best I can suggest is adding a conversion macro to your .xll, and having the user press the 'Fix-up' button when they open spreadsheets that have not been converted yet.

This is the solution I use at last. I keep my xla addin, in xll, I register the same MyUDF with the same sigature(while, almost the same, optional parameters are not supported in xll, but are used in my xla) and set IsHidden = true. So there is only one MyUDF shows up in insert function list. When you type in =MyUDF and click function wizard, the version in xll shows up in function argument window. In Excel 2007, when I type =MyUDF, there is no function in drop down. In Excel 2010, when I type =MyUDF, there is a drop down. So the solution works better in Excel 2010.