Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
Hello all,
First of all, thanks in advance for reading this post - hopefully it'll make sense... We've got a bit of a problem here that we hope you can help us with. We've just developed a COM automation add-in in Visual Studio 2005 to replace an XLA add in. To ease the conversion, we named most of the functions exported by the COM library to the same name as those in the XLA file. However, our problem is that on removing the XLA file and adding in the COM automation add-in to a workbook, the excel spreadsheets cannot reference the new method calls which have the same name as the old calls in the XLA. If we create a new worksheet and add in the automation item, then the calls work fine. For example, the UDF call CBSpot existed in the XLA. When we remove the XLA and add in the automation DLL CBSpot can't be resolved. However, if we fully qualify it (PricingLib.Connect.CBSpot) then it will work, but Excel will then automatically remove the ProgID (change it back to CBSpot), and the call will then fail. What we think is that somehow, the old XLA is still known of by the workbook and that is what is referenced. Any help would be most appreciated, since we're at the end of our tether here! Cheers, Dave Thompson Commerzbank AG, London Branch |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
Try going to each sheet and doing
Edit=Replace what: = with: ZZ= this will make all your formulas text strings. Once you have no formulas in the workbook, save and close it. Now open it and reverse the procedure and see if that helps. -- Regards, Tom Ogilvy "DaveThompson" wrote: Hello all, First of all, thanks in advance for reading this post - hopefully it'll make sense... We've got a bit of a problem here that we hope you can help us with. We've just developed a COM automation add-in in Visual Studio 2005 to replace an XLA add in. To ease the conversion, we named most of the functions exported by the COM library to the same name as those in the XLA file. However, our problem is that on removing the XLA file and adding in the COM automation add-in to a workbook, the excel spreadsheets cannot reference the new method calls which have the same name as the old calls in the XLA. If we create a new worksheet and add in the automation item, then the calls work fine. For example, the UDF call CBSpot existed in the XLA. When we remove the XLA and add in the automation DLL CBSpot can't be resolved. However, if we fully qualify it (PricingLib.Connect.CBSpot) then it will work, but Excel will then automatically remove the ProgID (change it back to CBSpot), and the call will then fail. What we think is that somehow, the old XLA is still known of by the workbook and that is what is referenced. Any help would be most appreciated, since we're at the end of our tether here! Cheers, Dave Thompson Commerzbank AG, London Branch |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
Dave,
If you have a Public creatable class that contains the functions provided by the VS2005 project, you should be able to call those functions in the VS2005 library directly from a worksheet cell if you load that class's ProgID as an Automation Add-In (from the regular XLA add-ins dialog,not the COM add-ins dialog). I believe that ProgID should be registered in HKCU for each user rather than HKLM for all users. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DaveThompson" wrote in message oups.com... Hello all, First of all, thanks in advance for reading this post - hopefully it'll make sense... We've got a bit of a problem here that we hope you can help us with. We've just developed a COM automation add-in in Visual Studio 2005 to replace an XLA add in. To ease the conversion, we named most of the functions exported by the COM library to the same name as those in the XLA file. However, our problem is that on removing the XLA file and adding in the COM automation add-in to a workbook, the excel spreadsheets cannot reference the new method calls which have the same name as the old calls in the XLA. If we create a new worksheet and add in the automation item, then the calls work fine. For example, the UDF call CBSpot existed in the XLA. When we remove the XLA and add in the automation DLL CBSpot can't be resolved. However, if we fully qualify it (PricingLib.Connect.CBSpot) then it will work, but Excel will then automatically remove the ProgID (change it back to CBSpot), and the call will then fail. What we think is that somehow, the old XLA is still known of by the workbook and that is what is referenced. Any help would be most appreciated, since we're at the end of our tether here! Cheers, Dave Thompson Commerzbank AG, London Branch |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
Tom,
Thanks for your input. We've just tried that, and unfortunately it doesn't work; it just goes back to the old problem once we've found/ replaced everything. Good idea though - any more? Cheers, Dave On 26 Feb, 13:28, Tom Ogilvy wrote: Try going to each sheet and doing Edit=Replace what: = with: ZZ= this will make all your formulas text strings. Once you have no formulas in the workbook, save and close it. Now open it and reverse the procedure and see if that helps. -- Regards, Tom Ogilvy "DaveThompson" wrote: Hello all, First of all, thanks in advance for reading this post - hopefully it'll make sense... We've got a bit of a problem here that we hope you can help us with. We've just developed a COM automation add-in in Visual Studio 2005 to replace an XLA add in. To ease the conversion, we named most of the functions exported by the COM library to the same name as those in the XLA file. However, our problem is that on removing the XLA file and adding in the COM automation add-in to a workbook, the excel spreadsheets cannot reference the new method calls which have the same name as the old calls in the XLA. If we create a new worksheet and add in the automation item, then the calls work fine. For example, the UDF call CBSpot existed in the XLA. When we remove the XLA and add in the automation DLL CBSpot can't be resolved. However, if we fully qualify it (PricingLib.Connect.CBSpot) then it will work, but Excel will then automatically remove the ProgID (change it back to CBSpot), and the call will then fail. What we think is that somehow, the old XLA is still known of by the workbook and that is what is referenced. Any help would be most appreciated, since we're at the end of our tether here! Cheers, Dave Thompson Commerzbank AG, London Branch |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
Chip,
Thanks for your input. We've already got Excel "seeing" the exposed functions in the public COM-visible class, and we can use them if we create a new Excel session and start typing our UDFs in cells. The problem occurs when we load an Excel workbook that references a VBA add-in that contains functions with the same names. Even if we remove the XLA VBA add-in and load in our COM add-in, the Excel sheets can't "see" the new functions from the new COM add-in. If we fully qualify it with the ProgId (i.e. PricingLibrary.Connect.CBSpot) then it'll work, but Excel will immediately take off the "PricingLibrary.Connect" qualifier and it'll go back to failing with "#NAME". To be honest with you, I'm thinking this may be a bug in Excel! Thanks everyone for your assistance so far! Dave On 26 Feb, 13:58, "Chip Pearson" wrote: Dave, If you have a Public creatable class that contains the functions provided by the VS2005 project, you should be able to call those functions in the VS2005 library directly from a worksheet cell if you load that class's ProgID as an Automation Add-In (from the regular XLA add-ins dialog,not the COM add-ins dialog). I believe that ProgID should be registered in HKCU for each user rather than HKLM for all users. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLCwww.cpearson.com (email address is on the web site) "DaveThompson" wrote in message oups.com... Hello all, First of all, thanks in advance for reading this post - hopefully it'll make sense... We've got a bit of a problem here that we hope you can help us with. We've just developed a COM automation add-in in Visual Studio 2005 to replace an XLA add in. To ease the conversion, we named most of the functions exported by the COM library to the same name as those in the XLA file. However, our problem is that on removing the XLA file and adding in the COM automation add-in to a workbook, the excel spreadsheets cannot reference the new method calls which have the same name as the old calls in the XLA. If we create a new worksheet and add in the automation item, then the calls work fine. For example, the UDF call CBSpot existed in the XLA. When we remove the XLA and add in the automation DLL CBSpot can't be resolved. However, if we fully qualify it (PricingLib.Connect.CBSpot) then it will work, but Excel will then automatically remove the ProgID (change it back to CBSpot), and the call will then fail. What we think is that somehow, the old XLA is still known of by the workbook and that is what is referenced. Any help would be most appreciated, since we're at the end of our tether here! Cheers, Dave Thompson Commerzbank AG, London Branch |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
Have you tried removing the .xla from the registry?
RBS "DaveThompson" wrote in message oups.com... Hello all, First of all, thanks in advance for reading this post - hopefully it'll make sense... We've got a bit of a problem here that we hope you can help us with. We've just developed a COM automation add-in in Visual Studio 2005 to replace an XLA add in. To ease the conversion, we named most of the functions exported by the COM library to the same name as those in the XLA file. However, our problem is that on removing the XLA file and adding in the COM automation add-in to a workbook, the excel spreadsheets cannot reference the new method calls which have the same name as the old calls in the XLA. If we create a new worksheet and add in the automation item, then the calls work fine. For example, the UDF call CBSpot existed in the XLA. When we remove the XLA and add in the automation DLL CBSpot can't be resolved. However, if we fully qualify it (PricingLib.Connect.CBSpot) then it will work, but Excel will then automatically remove the ProgID (change it back to CBSpot), and the call will then fail. What we think is that somehow, the old XLA is still known of by the workbook and that is what is referenced. Any help would be most appreciated, since we're at the end of our tether here! Cheers, Dave Thompson Commerzbank AG, London Branch |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
Hi RBS,
I didn't even realise that XLA files registered themselves, but a quick sweep of the registry through regedit doesn't yield any references to the XLA. Cheers, Dave On 26 Feb, 14:34, "RB Smissaert" wrote: Have you tried removing the .xla from the registry? RBS "DaveThompson" wrote in message oups.com... Hello all, First of all, thanks in advance for reading this post - hopefully it'll make sense... We've got a bit of a problem here that we hope you can help us with. We've just developed a COM automation add-in in Visual Studio 2005 to replace an XLA add in. To ease the conversion, we named most of the functions exported by the COM library to the same name as those in the XLA file. However, our problem is that on removing the XLA file and adding in the COM automation add-in to a workbook, the excel spreadsheets cannot reference the new method calls which have the same name as the old calls in the XLA. If we create a new worksheet and add in the automation item, then the calls work fine. For example, the UDF call CBSpot existed in the XLA. When we remove the XLA and add in the automation DLL CBSpot can't be resolved. However, if we fully qualify it (PricingLib.Connect.CBSpot) then it will work, but Excel will then automatically remove the ProgID (change it back to CBSpot), and the call will then fail. What we think is that somehow, the old XLA is still known of by the workbook and that is what is referenced. Any help would be most appreciated, since we're at the end of our tether here! Cheers, Dave Thompson Commerzbank AG, London Branch |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
I've never used COM addins, so this may not work--but this technique has worked
when someone creates a UDF in a specific workbook, then decides to move that UDF to an addin. In this situation, the formula continues to want to find the UDF in the original workbook. Saved from a previous post: After I moved the code from one workbook's project to the other and saved both files (one as an addin), I did this in the "regular" workbook (.xls) (with the ..xla still open). Insert|Name|define myFunc (refer to any cell) Add Then I deleted that name. insert|name|define myfunc delete Then I did edit|replace what: = (equal sign) with: = (equal sign) replace all Excel was smart enough to reevaluate this function from the xla. ========== Maybe you'll find that creating|deleting a name will work for you. DaveThompson wrote: Hello all, First of all, thanks in advance for reading this post - hopefully it'll make sense... We've got a bit of a problem here that we hope you can help us with. We've just developed a COM automation add-in in Visual Studio 2005 to replace an XLA add in. To ease the conversion, we named most of the functions exported by the COM library to the same name as those in the XLA file. However, our problem is that on removing the XLA file and adding in the COM automation add-in to a workbook, the excel spreadsheets cannot reference the new method calls which have the same name as the old calls in the XLA. If we create a new worksheet and add in the automation item, then the calls work fine. For example, the UDF call CBSpot existed in the XLA. When we remove the XLA and add in the automation DLL CBSpot can't be resolved. However, if we fully qualify it (PricingLib.Connect.CBSpot) then it will work, but Excel will then automatically remove the ProgID (change it back to CBSpot), and the call will then fail. What we think is that somehow, the old XLA is still known of by the workbook and that is what is referenced. Any help would be most appreciated, since we're at the end of our tether here! Cheers, Dave Thompson Commerzbank AG, London Branch -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
Hi Dave,
Thanks for the reply, but unfortunately this doesn't work either. I think we're going to have to go with copying the sheets manually to another workbook and then copying any VBA code by hand. Not an ideal solution by any stretch of the imagination, but one of the only solutions I can see to Excel's "quirks". Thanks, Dave On 26 Feb, 15:55, Dave Peterson wrote: I've never used COM addins, so this may not work--but this technique has worked when someone creates a UDF in a specific workbook, then decides to move that UDF to an addin. In this situation, the formula continues to want to find the UDF in the original workbook. Saved from a previous post: After I moved the code from one workbook's project to the other and saved both files (one as an addin), I did this in the "regular" workbook (.xls) (with the .xla still open). Insert|Name|define myFunc (refer to any cell) Add Then I deleted that name. insert|name|define myfunc delete Then I did edit|replace what: = (equal sign) with: = (equal sign) replace all Excel was smart enough to reevaluate this function from the xla. ========== Maybe you'll find that creating|deleting a name will work for you. DaveThompson wrote: Hello all, First of all, thanks in advance for reading this post - hopefully it'll make sense... We've got a bit of a problem here that we hope you can help us with. We've just developed a COM automation add-in in Visual Studio 2005 to replace an XLA add in. To ease the conversion, we named most of the functions exported by the COM library to the same name as those in the XLA file. However, our problem is that on removing the XLA file and adding in the COM automation add-in to a workbook, the excel spreadsheets cannot reference the new method calls which have the same name as the old calls in the XLA. If we create a new worksheet and add in the automation item, then the calls work fine. For example, the UDF call CBSpot existed in the XLA. When we remove the XLA and add in the automation DLL CBSpot can't be resolved. However, if we fully qualify it (PricingLib.Connect.CBSpot) then it will work, but Excel will then automatically remove the ProgID (change it back to CBSpot), and the call will then fail. What we think is that somehow, the old XLA is still known of by the workbook and that is what is referenced. Any help would be most appreciated, since we're at the end of our tether here! Cheers, Dave Thompson Commerzbank AG, London Branch -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - COM
Hello all,
Thanks for your help, but the problem has now been solved by simply changing the names of the new functions (by adding an extra character) and then renaming all references programatically. The problem with Excel "seeing" the old XLA hasn't been solved; the KB article at http://support.microsoft.com/kb/286305/en-us ("PRB: Automation Add-In Function Binds to Excel Built-In Function with the Same Name") contains some pointers to the problem but annoyingly the solution is simply to rename the functions. Cheers all, Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|