locked
Calling .NET dll from VBA RRS feed

  • Question

  • Hello,

           I have created .NET dll to call from VBA. I did checked the option "COM Visible = true" in settings. After compilation it created .tlb file for the .net dll.

          I did registered with regasm tool .both tlb and dll sucessfully registered.

          I opened the excel sheet and vba code then referenced to the tlb file and compiled.

          I can't create strong name for my .net dll as my dll using 3rd party dll reference which is not signed and throwing error if i tried   

          Where ever i try to create instance to .net class in vba i am getting below error.

    "Automation error
    The system cannot find the file specified. "

    Solutions tried :

    1.  I copied both dll and tlb file to system32 location.

    2.Genarated .reg file for the dll and loaded into registry

       but no luck yet... please help me if you have any solution. Thanks in advance... looking forward for your solution.

    Note: If i check the "Register for COM interop" checkbox in project properties it is working fine but if i want to deploy in client pc this option wont work i still need to register but that is not working.


    Thursday, February 9, 2012 7:34 PM

Answers

  • Hello srikanthpothineni,

    1. >> If i check the "Register for COM interop" checkbox in project properties it is working fine...

    1.1 By checking the "Register for COM interop" option in the IDE during development, the IDE will call regasm on the target assembly together with the /codebase option.

    1.2 This will cause regasm.exe to add the following registry entry for a COM-visible class exported from your assembly :

    HKEY_CLASSES_ROOT\CLSID\{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}\InprocServer32\CodeBase=<path to your assembly> 

    1.3 This will enable the CLR to locate your assembly by following the path to your assembly.

    1.4 Without the codebase path, the CLR will have to locate your assembly (and its dependencies) using the standard search algorithm.

    2. >> I copied both dll and tlb file to system32 location...

    2.1 One related technique that helps the CLR to locate required assemblies is to copy all assemblies (and their dependencies) to the same folder as the client application itself. However, the client application in your case will be Excel.

    2.2 This means that you must copy the assemblies into the same folder as the Excel application (not a good idea).

    3. Register to GAC.

    3.1 By far the best solution is to register the assemblies to the GAC.

    3.2 However, I understand that one of your dependency assemblies is not strong named hence this may not be possible.

    4. It may be helpful to read up on the way the .NET runtime searches for assemblies (refer to “How the Runtime Locates Assemblies” (http://msdn.microsoft.com/en-us/library/yx7xezcf(v=vs.71).aspx)).

    5. Best of luck.

    - Bio.


    Please visit my blog : http://limbioliong.wordpress.com/

    • Marked as answer by Paul Zhou Thursday, February 23, 2012 9:43 AM
    Friday, February 10, 2012 3:17 AM

All replies

  • Hello srikanthpothineni,

    1. >> If i check the "Register for COM interop" checkbox in project properties it is working fine...

    1.1 By checking the "Register for COM interop" option in the IDE during development, the IDE will call regasm on the target assembly together with the /codebase option.

    1.2 This will cause regasm.exe to add the following registry entry for a COM-visible class exported from your assembly :

    HKEY_CLASSES_ROOT\CLSID\{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}\InprocServer32\CodeBase=<path to your assembly> 

    1.3 This will enable the CLR to locate your assembly by following the path to your assembly.

    1.4 Without the codebase path, the CLR will have to locate your assembly (and its dependencies) using the standard search algorithm.

    2. >> I copied both dll and tlb file to system32 location...

    2.1 One related technique that helps the CLR to locate required assemblies is to copy all assemblies (and their dependencies) to the same folder as the client application itself. However, the client application in your case will be Excel.

    2.2 This means that you must copy the assemblies into the same folder as the Excel application (not a good idea).

    3. Register to GAC.

    3.1 By far the best solution is to register the assemblies to the GAC.

    3.2 However, I understand that one of your dependency assemblies is not strong named hence this may not be possible.

    4. It may be helpful to read up on the way the .NET runtime searches for assemblies (refer to “How the Runtime Locates Assemblies” (http://msdn.microsoft.com/en-us/library/yx7xezcf(v=vs.71).aspx)).

    5. Best of luck.

    - Bio.


    Please visit my blog : http://limbioliong.wordpress.com/

    • Marked as answer by Paul Zhou Thursday, February 23, 2012 9:43 AM
    Friday, February 10, 2012 3:17 AM
  • Hello,

        Thanks for your solution and it worked fine by using the /codebase and working fine in Dev. Now i deployed the DLL to CITRIX server where multiple users can use excel from CITRIX server and launch their spreadsheet from shared drive. I did registered with codebase and generated .tlb file.

      Now i am trying to test the DLL and getting "File or assembly name <DLL Name>, or one of its dependencies, was not found."

    It has been working fine in Dev server but when i deployed i am getting above error.

    Please help me on this. Thanks.


    Srikanth

    Monday, March 5, 2012 4:39 PM