Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Replace XLA with XLL function
Hello,
I'm looking for a way to replace some worksheet functions defined in an XLA by native counterparts exported from an XLL add-in. When deleting the functions from the XLA and adding them to the XLL, all existing workbooks show "#NAME?" when recalculating these functions. I have to add that the XLA is not accessed as a "classic" add-in, but instead added using Tools-References from the VBA editor. The only way I was able to update to the new XLL function was by renaming "'C:\Full\Path\To\AddIn\Name.xla'!FunctionName " to "SomethingSilly", saving, closing, reopening and again renaming "SomethingSilly" to "FunctionName". Is there an easier way? I'm asking since I need to upgrade a lot of workbooks, with some of them not available on the server (that is, the upgrade should run at the end-user). Every help or hint appreciated! Jens. -- Replace MSDN with my first name when replying to my email address! |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Replace XLA with XLL function
Hi Jens
I'm not at all sure about what I'm about to say, but it sounds like it might be a registry issue, i.e. Excel writing something into the registry and then not being able to work out what function it's supposed to be using unless you force it to recognise that it's registry entry is out of date by restarting Excel with the function in the XLA missing. Have you looked into this as a possibility? Regards Steve Dalton "Jens Thiel" wrote in message ... Hello, I'm looking for a way to replace some worksheet functions defined in an XLA by native counterparts exported from an XLL add-in. When deleting the functions from the XLA and adding them to the XLL, all existing workbooks show "#NAME?" when recalculating these functions. I have to add that the XLA is not accessed as a "classic" add-in, but instead added using Tools-References from the VBA editor. The only way I was able to update to the new XLL function was by renaming "'C:\Full\Path\To\AddIn\Name.xla'!FunctionName " to "SomethingSilly", saving, closing, reopening and again renaming "SomethingSilly" to "FunctionName". Is there an easier way? I'm asking since I need to upgrade a lot of workbooks, with some of them not available on the server (that is, the upgrade should run at the end-user). Every help or hint appreciated! Jens. -- Replace MSDN with my first name when replying to my email address! |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Replace XLA with XLL function
Hi Steve,
I think that the workbook itself carries some information about the source of external functions. Otherwise it wouldn't be able to show the last known location when moving the XLA. In this case, the XLA is not even added in the usual way (using the add-in manager), but "referenced" from VBE. I haven't heard of any registry entries being involved, but I will run a search in the background to be sure. Thank you anyway, Jens. -- Replace MSDN with my first name when replying to my email address! "Steve Dalton" wrote Hi Jens I'm not at all sure about what I'm about to say, but it sounds like it might be a registry issue, i.e. Excel writing something into the registry and then not being able to work out what function it's supposed to be using unless you force it to recognise that it's registry entry is out of date by restarting Excel with the function in the XLA missing. Have you looked into this as a possibility? Regards Steve Dalton "Jens Thiel" wrote in message ... Hello, I'm looking for a way to replace some worksheet functions defined in an XLA by native counterparts exported from an XLL add-in. When deleting the functions from the XLA and adding them to the XLL, all existing workbooks show "#NAME?" when recalculating these functions. I have to add that the XLA is not accessed as a "classic" add-in, but instead added using Tools-References from the VBA editor. The only way I was able to update to the new XLL function was by renaming "'C:\Full\Path\To\AddIn\Name.xla'!FunctionName " to "SomethingSilly", saving, closing, reopening and again renaming "SomethingSilly" to "FunctionName". Is there an easier way? I'm asking since I need to upgrade a lot of workbooks, with some of them not available on the server (that is, the upgrade should run at the end-user). Every help or hint appreciated! Jens. -- Replace MSDN with my first name when replying to my email address! |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Replace XLA with XLL function
"Jens Thiel" wrote in message ... Hello, I'm looking for a way to replace some worksheet functions defined in an XLA by native counterparts exported from an XLL add-in. When deleting the functions from the XLA and adding them to the XLL, all existing workbooks show "#NAME?" when recalculating these functions. I have to add that the XLA is not accessed as a "classic" add-in, but instead added using Tools-References from the VBA editor. The only way I was able to update to the new XLL function was by renaming "'C:\Full\Path\To\AddIn\Name.xla'!FunctionName " to "SomethingSilly", saving, closing, reopening and again renaming "SomethingSilly" to "FunctionName". Is there an easier way? I'm asking since I need to upgrade a lot of workbooks, with some of them not available on the server (that is, the upgrade should run at the end-user). Every help or hint appreciated! Jens. -- Replace MSDN with my first name when replying to my email address! Hmm. Let' see if I understand this. What youre saying is that you used to have something like this =C:\Full\Path\To\AddIn\Name.xla'!MyUdf(Param1, Param2,...) You have since replaced the functionality in Name.xla with equal functionality in an xll. If you have done so correctly, you don't need a path at all. Instead, the above expression should be relaced by =MyUdf(Param1, Param2,...) Here's a link that shows how to replace hyperlinks: http://www.j-walk.com/ss/excel/usertips/tip031.htm I hope this will help. Best Regards, Fredrik |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Please disregard my previous reply
I think you should try this
1) Replace 'C:\Full\Path\To\AddIn\Name.xla'! with nothing. Leave the field blank 2) Remove the reference to Name.xla 3) Load your xll 4) Replace = with =. This will force excel to re-evaluate all functions Best Regards, Fredrik |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Replace XLA with XLL function
"Fredrik Wahlgren" wrote
Hmm. Let' see if I understand this. What youre saying is that you used to have something like this =C:\Full\Path\To\AddIn\Name.xla'!MyUdf(Param1, Param2,...) You have since replaced the functionality in Name.xla with equal functionality in an xll. If you have done so correctly, you don't need a path at all. Instead, the above expression should be relaced by =MyUdf(Param1, Param2,...) That was my initial thought, but replacing it doesn't work and you get "#NAME?" instead. The MyUdf function from the XLL seems to be hidden by fragements (in the workbook) of the deleted XLA equivalent. It is available in other workbooks, and shows up in the function wizard as well. Here's a link that shows how to replace hyperlinks: http://www.j-walk.com/ss/excel/usertips/tip031.htm I hope this will help. Nope, completely unrelated :-( Any other idea? Jens. -- Replace MSDN with my first name when replying to my email address! |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Please disregard my previous reply
"Fredrik Wahlgren" wrote
I think you should try this 1) Replace 'C:\Full\Path\To\AddIn\Name.xla'! with nothing. Leave the field blank 2) Remove the reference to Name.xla When referenced using VBE ToolsReferences, the Name.xla shows up in the VBAProject "explorer". It can not be removed using the GUI, but disabling security for VBAProject allows to do Public Sub RemoveFbA() Set r = ThisWorkbook.VBProject.References.Item("FbAProject ") ThisWorkbook.VBProject.References.Remove r End Sub 3) Load your xll 4) Replace = with =. This will force excel to re-evaluate all functions Doesn't work. Only if I rename _ALL_ functions (e.g. MyUdf-XxxMyUdf) that previously referenced Name.xla, save, close, reopen and rename them back (XxxMyUdf-MyUdf) the XLL function is finally called. It seems that as long a single UDF from the XLA is left, the once existing association with Name.xla is kept by the workbook. And to make it even more complicated: I can not remove the Name.xla completely as only _some_ functions have been replaced. *sigh* Thanks a lot for the idea. Removing the reference wasn't tried before (only removing the Name.xla completely). Jens. -- Replace MSDN with my first name when replying to my email address! |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Please disregard my previous reply
"Jens Thiel" wrote in message ... "Fredrik Wahlgren" wrote I think you should try this 1) Replace 'C:\Full\Path\To\AddIn\Name.xla'! with nothing. Leave the field blank 2) Remove the reference to Name.xla When referenced using VBE ToolsReferences, the Name.xla shows up in the VBAProject "explorer". It can not be removed using the GUI, but disabling security for VBAProject allows to do Public Sub RemoveFbA() Set r = ThisWorkbook.VBProject.References.Item("FbAProject ") ThisWorkbook.VBProject.References.Remove r End Sub 3) Load your xll 4) Replace = with =. This will force excel to re-evaluate all functions Doesn't work. Only if I rename _ALL_ functions (e.g. MyUdf-XxxMyUdf) that previously referenced Name.xla, save, close, reopen and rename them back (XxxMyUdf-MyUdf) the XLL function is finally called. It seems that as long a single UDF from the XLA is left, the once existing association with Name.xla is kept by the workbook. And to make it even more complicated: I can not remove the Name.xla completely as only _some_ functions have been replaced. *sigh* Thanks a lot for the idea. Removing the reference wasn't tried before (only removing the Name.xla completely). Jens. -- Replace MSDN with my first name when replying to my email address! Hmm.. Can you rename the function so that you include the equal sign as well. Now Excel won't think of it as a function. Shouldn't it be possible to remove the reference now? Having done that, replace MyUdf with =XxxMyUdf. Of course, this won't work if you have a function similar to =A1+'C:\Full\Path\To\AddIn\Name.xla'!MyUdf (...) / Fredrik |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Please disregard my previous reply
"Fredrik Wahlgren" wrote
Hmm.. Can you rename the function so that you include the equal sign as well. Now Excel won't think of it as a function. Shouldn't it be possible to remove the reference now? One would think but... Nope. Not from the GUI (given that I'm looking at the right places), only programatically. Jens. -- Replace MSDN with my first name when replying to my email address! |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Replace XLA with XLL function
So if the information is stored in the workbook itself, then it might be
possible to create a program that locates the record in the workbook file (opened as a binary file) and modifies it directly. I'm sure you have a copy of the SDK text but perhaps, like me, have never bothered to look too much at the BIFF section. The BIFF version in the SDK is now out of date (since Excel 2000 I think) so you might need to find a more up-to-date equivalent. "Jens Thiel" wrote in message ... Hi Steve, I think that the workbook itself carries some information about the source of external functions. Otherwise it wouldn't be able to show the last known location when moving the XLA. In this case, the XLA is not even added in the usual way (using the add-in manager), but "referenced" from VBE. I haven't heard of any registry entries being involved, but I will run a search in the background to be sure. Thank you anyway, Jens. -- Replace MSDN with my first name when replying to my email address! "Steve Dalton" wrote Hi Jens I'm not at all sure about what I'm about to say, but it sounds like it might be a registry issue, i.e. Excel writing something into the registry and then not being able to work out what function it's supposed to be using unless you force it to recognise that it's registry entry is out of date by restarting Excel with the function in the XLA missing. Have you looked into this as a possibility? Regards Steve Dalton "Jens Thiel" wrote in message ... Hello, I'm looking for a way to replace some worksheet functions defined in an XLA by native counterparts exported from an XLL add-in. When deleting the functions from the XLA and adding them to the XLL, all existing workbooks show "#NAME?" when recalculating these functions. I have to add that the XLA is not accessed as a "classic" add-in, but instead added using Tools-References from the VBA editor. The only way I was able to update to the new XLL function was by renaming "'C:\Full\Path\To\AddIn\Name.xla'!FunctionName " to "SomethingSilly", saving, closing, reopening and again renaming "SomethingSilly" to "FunctionName". Is there an easier way? I'm asking since I need to upgrade a lot of workbooks, with some of them not available on the server (that is, the upgrade should run at the end-user). Every help or hint appreciated! Jens. -- Replace MSDN with my first name when replying to my email address! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please disregard my previous reply
Hi Jens,
As far as I know, xll is used from excel 97. I think so far we can try to write a ComAddin which is newer addin technology. We can build comaddin in many language(VC++,VC#.NET,VB.NET,VB6), Here is many references you may have a check. SAMPLE: Comaddin.exe Office 2000 COM Add-In Written in Visual C++ (230689) http://support.microsoft.com/default...B;EN-US;230689 How To Build an Office 2000 COM Add-In in Visual Basic (238228) http://support.microsoft.com/default...B;EN-US;238228 How To Build an Office COM Add-in by Using Visual Basic .NET (302896) http://support.microsoft.com/default...B;EN-US;302896 How To Build an Office COM Add-in by Using Visual C# .NET (302901) http://support.microsoft.com/default...B;EN-US;302901 INFO: Excel COM Add-ins and Automation Add-ins (291392) http://support.microsoft.com/default...B;EN-US;291392 Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please disregard my previous reply
""Peter Huang"" wrote.
Hi Jens, As far as I know, xll is used from excel 97. I think so far we can try to write a ComAddin which is newer addin technology. Hi Peter, the problem is obviously not writing the new add-in, but getting rid of "hidden" references to functions that have been deleted from the XLA (referenced from VBE, to make it more complicated). See the this thread for more information. Although it sounds trivial - it isn't. Or everybody looking into this doesn't see the obvious? There are 500+ actively used worbooks affected. A solution would be appreciated very much. Best regards, Jens. -- Replace MSDN with my first name when replying to my email address! |
#13
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk
|
|||
|
|||
Replace XLA with XLL function
Hi Steve,
you are right: I never bothered with the BIFF section. Spending a few minutes, it turns out that in a parsed Formula (444ff), a ptgName tokens's ixti field (page 456) references external workbooks and names (308-312). However, the XLA in question has been attached using the VBE, and the SDK says (page 262) that the VBA PROJECT storage is not documented. I do not know about a published BIFF reference for Excel 2000 or later. Jens. -- Replace MSDN with my first name when replying to my email address! "Steve Dalton" wrote in message ... So if the information is stored in the workbook itself, then it might be possible to create a program that locates the record in the workbook file (opened as a binary file) and modifies it directly. I'm sure you have a copy of the SDK text but perhaps, like me, have never bothered to look too much at the BIFF section. The BIFF version in the SDK is now out of date (since Excel 2000 I think) so you might need to find a more up-to-date equivalent. "Jens Thiel" wrote in message ... Hi Steve, I think that the workbook itself carries some information about the source of external functions. Otherwise it wouldn't be able to show the last known location when moving the XLA. In this case, the XLA is not even added in the usual way (using the add-in manager), but "referenced" from VBE. I haven't heard of any registry entries being involved, but I will run a search in the background to be sure. Thank you anyway, Jens. -- Replace MSDN with my first name when replying to my email address! "Steve Dalton" wrote Hi Jens I'm not at all sure about what I'm about to say, but it sounds like it might be a registry issue, i.e. Excel writing something into the registry an d then not being able to work out what function it's supposed to be using unless you force it to recognise that it's registry entry is out of date by restarting Excel with the function in the XLA missing. Have you looked into this as a possibility? Regards Steve Dalton "Jens Thiel" wrote in message ... Hello, I'm looking for a way to replace some worksheet functions defined in an XLA by native counterparts exported from an XLL add-in. When deleting the functions from the XLA and adding them to the XLL, all existing workbooks show "#NAME?" when recalculating these functions. I have to add that the XLA is not accessed as a "classic" add-in, but instead added using Tools-References from the VBA editor. The only way I was able to update to the new XLL function was by renaming "'C:\Full\Path\To\AddIn\Name.xla'!FunctionName " to "SomethingSilly", saving, closing, reopening and again renaming "SomethingSilly" to "FunctionName". Is there an easier way? I'm asking since I need to upgrade a lot of workbooks, with some of them not available on the server (that is, the upgrade should run at the end-user). Every help or hint appreciated! Jens. -- Replace MSDN with my first name when replying to my email address! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you have a value created by a function replace the function? | Excel Worksheet Functions | |||
Need Help with Replace Function | Excel Worksheet Functions | |||
How i can replace function | Excel Worksheet Functions | |||
Replace Function | Excel Programming | |||
Replace Function | Excel Programming |