Use COM object from Excel VBA - made in .NET "ActiveX component can't create object"

[brazilnut52]

I am going to outline the steps I go through to produce the problem. Hopefully this will help you understand the problem better.

I have created a simple COM DLL in .NET by using the COM class template and by setting output to a type library (DLL). All the object does is return a string value. At this point I have not checked the option to register for COM interop in Visual Studio.

So I go into Excel (where I want to use the object). Go to VB Editor and Tools -> References and to Add Reference. I browse until I find the \bin directory of the project. Select the .dll file outputted by the program. Then click open -> I get the message "Can't add reference to the specified file." So here is my first question: Why not?

So then I go to my project in Visual Studio. Highlight the project -> click properties ->go to Build -> and check "Register for COM interop" -> then I rebuild my solution. After this build there is another file in the \bin directory with the same name as the .dll but has the extension .tlb (Type Library).

So I go back into Excel. Go to VB Editor -> Tools -> References and to Add Reference. I browse until I find the \bin directory of the project. This time however I select the .tlb file outputted by the program. Then click open -> I don’t get an error message so this is good. So here is my second question: Why does the .tlb work and not the .dll at this point?

Now after this step. I create the object in VBA code. Compile and run the macro and sure enough it returns the string and puts in the right cell. Great! But wait. Now many more people need this object to work with Excel.

So I copy the Excel Workbook and everything in the \bin directory from my project to another person’s computer. Then I open the Excel workbook -> then to VB Editor -> References to double check the path to the .tlb file -> (then compile and save for peace of mind). But here is the big problem... when I run the macro on another person's computer I get the message "ActiveX component can't create object." And I get this at the line where I say: Set testObj = New Object.

My ideas... could it be that I am an Admin on my PC but not the other? If so, how do I get around this. Or is Visual Studio doing an extra step in the compile/build process that I’m not replicating on the other PC? Does it need to be an global assembly?

Also, .NET is installed on the other PC and I have tried to register the .DLL, but this is unsuccessful as well? I guess that is another question: why?

If someone could help me out here I would be very grateful. I have been trying to figure out a solution for a long time. I just don’t understand why everything works fine on my PC but won't run on another.

Thanks for being patient.
brazilnut

[brazilnut52]

bump

[techgnome]

the TLB file holds the mapping to the COM compatible interface. That's why the ref to the TLB works while the DLL doesn't.

As for getting it to work on other machines, you'll need to register the TLB file with the system. Just setting the reference isn't enough.

regsvr32 C:\Your_Path_Here\YourClass.tlb

TG

[brazilnut52]

Thanks for the response. I tried to register the .tlb but I received an error message. The message was "...\className.tlb is not an executable file and no registration helper is registered for this file type." So then I tried to register the .dll (which I have tried many times before) and received the message: "...\className.dll was loaded but the DLLRegisterService entry point was not found..." I then tried to run the VB macro from within Excel and received the same error message as before " ActiveX can't create object...."

Could the rights on the computer have anything to do with it? I have admin rights on the development PC but not on the test (deploy) machine.

Thanks,
brazilnut

[brazilnut52]

I tried something interesting on the deployment computer. I ran the regasm.exe (Assembly Registration Tool) on the .dll with the option of creating a tlb file. So I ran: regasm "...\class.dll" /tlb:class2.tlb

The tool ran successfuly and it said the library was registred successfully. I named it class2.tlb so as not to overwrite the class.tlb already in the directory. Then I went into Excel changed the reference to the class2.tlb. Compiled and ran and received a new error message (I assume this is a step in the right direction). The new message read: "File or assembly name 'class', or one of its dependencies, was not found." Im assuming the message is refering to the .dll file but it is located in the same directory as both of the .tlb files. Any ideas?

brazilnut