• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1105
  • Last Modified:

Unable to call COM object written in C# from Excel

I was to use code written in C# from an Excel VBA Function.  I have written a simple c# object like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;

namespace ExcelTest
{
    [ComVisible(true), Guid("B74A3D2A-7F80-40b4-9398-FB4BAD0BF234"), ProgId("ExcelTest.Class1")]
    public class Class1
    {
        public Class1()
        {
        }

        public void Test()
        {
            System.Windows.Forms.MessageBox.Show("Its working", "Yay!");
        }
    }
}

I am building it from Visual Studio 2005, and I checked the register for COM interop check box in the build properties.  I wrote a test VBS file that looks like this:

Dim FooObj

Set FooObj = CreateObject("ExcelTest.Class1")
FooObj.Test


The test VBS file works fine, but if I put the same code in an excel module and try to step through the code, execution of the function stops when CreateObject is called.

Does anybody know why it doesn't work from Excel or does somebody have suggestions on how I can trouble shoot this?

Thanks
0
tridedave
Asked:
tridedave
  • 4
  • 3
  • 3
2 Solutions
 
Bob LearnedCommented:
Since you are trying to work with COM, I would create an interface and make the class implement the interface.

Bob
0
 
jasonclarkeCommented:
You will either need to put the component in the same directory as Excel or in the GAC to allow Excel to pick it up.  Have you done that?
0
 
tridedaveAuthor Commented:
I did try putting the assembly in the same directory as Excel.exe at one point, but that didn't help.  The VBS script is able to locate the assembly even though the assembly  is not in the same directory as the VBS file.  I imagine that works becausell the location of the DLL is stored in the registry, but I think I will try putting the assembly in the GAC to see if it helps.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Bob LearnedCommented:
As long as you are registering the .NET assembly for COM correctly, it doesn't matter where it is located, since the reference path will be evaluated by looking in the Windows registry for the location.

This is what I was talking about:

Calling a .NET Component from a COM Component
http://msdn2.microsoft.com/en-us/library/ms973802.aspx

<Quote>There are two prerequisites to keep in mind when creating a .NET class that will be used by COM clients.

First, explicitly define an interface in your Visual Basic .NET code, and have the class implement the interface.

...

Secondly, any class that is to be visible to COM clients must be declared public.</Quote>

Bob
0
 
jasonclarkeCommented:
> As long as you are registering the .NET assembly for COM correctly, it doesn't matter where it is
> located, since the reference path will be evaluated by looking in the Windows registry for the location.

sorry - this is just wrong - the article you reference states that the DLL must be in the GAC - but this is usually not the only option - it is typically OK to store the assembly in the same directory.

The reason is that a .NET component is *not* a COM component - what happens to make a .NET component work like a COM component is that when you register it (using regasm - or whatever) - mscoree.dll (the .NET run-time) is registered as the handler for the COM class activation request.  The name of the class that .NET is also stored in the registry (the path of the .NET class library is *not* stored in the registry).  The .NET run-time then uses the normal .NET rules for finding the class: i.e. typically it will search the run-time directory and it will search the GAC.
0
 
jasonclarkeCommented:
> name of the class that .NET is also stored in the registry

this should be 'name of the class that .NET needs to instantiate/generate a CCW wrapper for is also stored in the registry'
0
 
tridedaveAuthor Commented:
I don't know why I'm having so much trouble with this.  I did this without much trouble like 4 years ago, and now I can't get it to work.  I tried putting the assembly in the GAC and I tried using an explicit interface but neither of those helped.

I found out that there is a registry value HKEY_LOCAL_MACHINE\Software\Microsoft\.NetFramework\CLRLoadLogDir which can be used to turn on some basic logging of loading the CLR.  Once I turn that on I get a log file called EXCEL.EXE.CLRLoad0.log that says:

Default version of the runtime on the machine: v1.1.4322
ERROR: Version v1.1.4322 is not present on the machine.

Well 1.1 is not installed on the machine.  I only have 2.0.  For the heck of it I installed 1.1, but then I stated getting errors in the fusion log saying the CLR was unable to read the manifest in the assembly.  (That makes sense the assembly is a 2.0 assembly built with Visual Studio 2005)

Just for comparison I also get a log file called WScript.exe.CLRLoad0.log when I run the VBS file I made which says:

Default version of the runtime on the machine: v2.0.50727
Decided on runtime: v2.0.50727

So the VBS file works because it loads the right version of the framework.  Does anybody have any idea why when I run the same code through Excel it trys to load the wrong version of the framework?

Thanks
0
 
Bob LearnedCommented:
I wonder if an app.config file with these settings might help:

<configuration>
   <startup>
      <requiredRuntime version="v2.0.50727"/>
     <supportedRuntime version="v2.0.50727"/>
    </startup>
</configuration>

Bob
0
 
jasonclarkeCommented:
This link seems to say the same sort of thing:

http://www.codeguru.com/forum/showthread.php?t=427509

I think Learned's solution should fix it.

I thought a bit more about the DLL location issue - you can force it to behave in a more 'standard' COM based way if you specify a 'Codebase' parameter when you use regasm (I think maybe this is default behaviour when Visual Studio registers a COM callable .NET component for you) - though I think this is not recommended for production code.  The .NET resolution process is as I stated above though - it is just changed to add the path specified in the Codebase once the other options have failed.  Apologies if this is what you were referring to in your statement...
0
 
tridedaveAuthor Commented:
I created a file called EXCEL.EXE.config and put it in the same directory as EXCEL.EXE and I put the following in the file and everything worked fine.

<?xml version ="1.0"?>
<configuration>
  <startup>
      <supportedRuntime version="v2.0.50727"/>
  </startup>
</configuration>

I'm not sure if this is a common problem for people with Excel 2003 and .net 2.0, or if its something odd about my machine.  If I was planning on trying to release my code to a large number of people, I might have a problem installing files into the office directory, but this will only be used by a dozen people in my office so it works for me.

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now