2 Replies Latest reply on Sep 11, 2013 2:56 PM by tinklerj

    Excel VBA addin calling .NET assembly calling PI-SDK: 64-bit COM interop issue?


      This issue may not be related to OSIsoft PI-SDK at all but it's hard to tell...


      I have an Excel addin written in VBA which calls a .NET assembly written by me (uses .NET Framework 4.0) which in turn calls PI-SDK.


      This used to work OK under Windows XP and Excel 2010 x86.


      Now I have been given a new PC with WIndows 7 x64 and Office x64.  The addin will no longer run (Error 429: ActiveX component can't create object).


      My .NET assembly is built (in VS2010) as MSIL (AnyCPU) so I would have thought it can run in 64-bit mode.  So maybe it is PI-SDK.


      I rebuild my assembly for 64-bit platform and make sure to use 64-bit PI-SDK references from the GAC.


      Here I hit a bug in Visual Studio: it doesn't automatically call 64-bit regasm to register a 64-bit COM object even when the project file tells it to.


      So I try entering the command by hand:


      C:\Windows\Microsoft.NET\Framework64\v4.0.30319\regasm MyAssembly.dll /tlb:MyAssembly.tlb


      This produces a MyAssembly.tlb file.  In Excel (Visual Basic) I select Tools > References and browse to this TLB file which loads OK.


      When my VBA addin makes a call to my assembly I get this error: Run-time error '-2147024894 (80070002)': Automation error, the system cannot find the file specified.


      If I look in the 64-bit COM viewer I can see my assembly's types in the registry but if I try to expand any of them I get the same error 80070002 (The system cannot find the file).


      I presume the file in question is MyAssembly.dll, but why can't it be found?  Is my regasm command in error?  NB. If I add the /codebase switch I get warnings and then an access denied error.

        • Re: Excel VBA addin calling .NET assembly calling PI-SDK: 64-bit COM interop issue?

          Hi Jeremy,


          This looks like a more general and less PI SDK related issue to me. Can you try creating a "Hello World!" add-in and see if it shows a similar behavior?


          Have you tried compiling your add in explicitly for 32 bit?


          We occasionally see automation error 429 with PI DataLink and other PI SDK based installations on 64-bit Windows / with 64-bit office. This OSIsoft Technical Support article may be helpful: KB00704 - Tagsearch in PI Client gets "Error: 429 ActiveX component can't create object Call Stack"


          Also, did you see additional details with error 429 - maybe in the Application Event log?

          • Re: Excel VBA addin calling .NET assembly calling PI-SDK: 64-bit COM interop issue?

            I sorted out the regasm issue.  You have to right click the command window shortcut and select "Run as administrator".  Apparently this is not the same as being logged in as Administrator.  It then accepts the codebase switch, but with a warning that the assembly should be signed.


            I wonder if there are any OSIsoft guidelines about targeting the 64-bit PI-SDK vs the 32-bit PI-SDK?  Is it possible to do this (in Visual Studio 2010) using a single codebase?  Can the 64-bit SDK only run in 64-bit code and the 32-bit version only in 32-bit code?


            Ideally you would be able to select the "AnyCPU" platform and have it target the 32-bit SDK, or the "x64" platform and target the 64-bit SDK.  By "target" I mean adjust the project references accordingly.  There doesn't seem to be any way to do this from within VS2010.  I tried modifying the .csproj project file directly with the following:


             [see attached text file - couldn't get the XML to format properly in code block]


            However, this doesn't seem to work too well.  Whichever platform configuration is selected, the PI-SDK references in VS2010 appear twice (once with a yellow warning overlay meaning "not found").  The valid references appear to always be the 64-bit ones (in C:\Program Files\PIPC\PISDK\PublicAssemblies\) rather than the 32-bit ones (in C:\Program Files (x86)\PIPC\PISDK\PublicAssemblies\).  If building the AnyCPU (32-bit) configuration, the 64-bit libraries are still used.


            The 64-bit COM object does work now.  I can build what I think is a 32-bit version (except I'm not sure about PI-SDK) but don't have a 32-bit Office system to try it on...


            Notes for getting it all to work in Visual Studio:


            Uncheck the setting "Register for COM interop".  (VS2010 internally uses 32-bit regasm for this).   Instead, create a post-build event as follows:

            "%Windir%\Microsoft.NET\Framework64\v4.0.30319\regasm" "$(TargetPath)" /tlb "$(TargetDir)$(TargetName).tlb" /codebase

            This 64-bit regasm can handle the 32-bit DLL as well so it works for all platforms.  Oddly, it doesn't seem to need you to run VS2010 as administrator.  Use the solution Configuration Manager and project settings to ensure that everything is built for the x64 platform when the x64 configuration is used, and for the AnyCPU platform when the AnyCPU configuration is used.