add in - Remove AddIn path from UDF in Excel formula

Question

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 FilesInstallation folderMyUDFs.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 FilesInstallation folderMyUDFs.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

Answer 1

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.