?
Solved

C# COM Callable DLL using Excel VBA - Error 80070002

Posted on 2006-06-07
4
Medium Priority
?
2,074 Views
Last Modified: 2007-11-27
I've created a C# COM callable DLL in Visual Studio 2005.  When I use it from Excel, there are no problems on the development machine - it works just as expected.  On any other machine I've tested on, I get Error #80070002 from Excel - file not found.  I've created a test project (extremely simple), and tried that as well, and get the same result.  The TLB file DOES show up in the "References" menu in Excel.

To go into detail, I will use the test project (since it's simple enough that I can put all of the code here):
I started a new Windows Class Library Project, and left everything default (I even left the names "ClassLibrary1" and "Class1").  I added what was neccessary to the Class1.cs file in order to test it.  The code for the Class1.cs file is below:
===========================================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;

namespace ClassLibrary1
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class Class1
    {
        public Class1()
        {
        }

        public int DoStuff()
        {
            return 256;
        }
    }
}
===========================================================
Now my entire class is complete.  Next I enabled COM-Visible and COM-Interop by right clicking on the Project and selecting Properties.  In the "Application" tab, I hit the "Assembly Information" button, and check "Make Assembly COM-Visible" box.  In the "Build" tab, I check the "Register for COM interop" box.  Now the DLL portion is complete.  I build the DLL as "release".

I then create a new Setup Project, to which I add the Primary Output from ClassLibrary1.  It autodetects the "ClassLibrary1.tlb" dependancy in the release folder.  I make sure in Configuration Manager that the Setup Project is being built for "release".  Then I rebuild the solution as "release".

I install the setup project on the development machine, and create a new Excel spreadsheet (using Excel XP).  In the spreadsheet, I add a reference to "ClassLibrary1" (which shows up correctly in the References menu).  I then create a macro as follows:
===========================================================
Public Sub tester()
    Dim x As Class1
    Set x = New Class1
    Debug.Print (x.DoStuff())
End Sub
===========================================================
On the development machine this runs fine, and in the debug window I get "256" (like I should).

On any other machine I do the above installation on, the macro does not work.  I get an error on "Set x = New Class1", which is the 80070002 error, no file found.  All machines I've tested on have the .NET Framework 2.0 installed, and I've used Microsoft's Component installer (http://www.microsoft.com/downloads/details.aspx?familyid=2a5e4ebc-651c-40aa-9525-1810af47c317&displaylang=en) to make sure I'm not missing anything that may be coming from MS.  The machines are both Windows 2000 and Windows XP.  On one machine, I installed the Framework 2.0 SDK, and have tried using regasm (regasm <dll name> /tlb), and still it does not work.

I hope I've described everything in enough detail (yes, I know, probably too much detail).  I don't know what else I have to include in the setup project, or if there's another way of deploying that will fix this issue.  Any help is greatly appreciated!

-  Kurtis W. Story
0
Comment
Question by:Fledgewing
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:dstanley9
ID: 16855008
Are you adding the assembly to the GAC?  If not, it will need to be local to the executable you are running (Excel, e.g.).  Are you creating a new Excel spreadsheet on the server and then adding a reference to the class library?  It sounds like either 1) the DLL is not in the GAC or is not getting registered properly, 2) the spreadsheet is looking for a different version, or 3) there could be a security issue accessing the DLL.

Try running regasm /u <dll name> and then run regasm <dll name> /verbose also to see if there are any registration issues.
0
 

Author Comment

by:Fledgewing
ID: 16855621
I tried rerunning regasm (unregistering, then registering verbose) as you suggested, and there were no registration issues.

The assembly is not being added to the GAC.  I tried uninstalling, and reinstalling it to the Excel directory (C:\Program Files\Microsoft Office\Office10\).  This still produces an error (though a different error, this is an "Automation Error" with no details.  I didn't think to write the number down at the time, but I would rather not install my DLL into the Excel directory in the first place, especially since the DLL should be runnable from different versions of Office).  I then tried installing it to the directory of the spreadsheet, though that did not work either.

So I thought I would try adding it to the GAC.  I signed the project, and moved the project output to the GAC in the Setup Project.  It installs fine, and when I look at the GAC in .NET Configuration Manager 2.0, I see my "ClassLibrary" in there OK.  The TLB is still in the install directory.  When I run the macro code this time, it first tries to shutdown my computer (the first time I tested with my saved spreadsheet, and it did shut down my computer, so I tried again with a new spreadsheet, and it asks me to save, so I cancel the save and it tells me "Cannot Quit Microsoft Excel").  Then it gives me the same error:
===============================
Run-time error '2147024894 (80070002)':
Automation error
The System cannot find the file specified.
===============================

As well, when I look into the directory of the TLB file, I find that it creates .TMP files that are copies of the TLB file, every time I try the macro.  These are named TBM_ _.tmp (where _ _ are two hex digits).

As for (3) the security issue, what kind of issue would it be?  I'm the admin user on all of the machines I'm testing on, and I'm testing everything from the local C:\ drive.  If it's a .NET security thing, how would I check and change this?

-  Kurtis W. Story


0
 
LVL 25

Accepted Solution

by:
dstanley9 earned 2000 total points
ID: 16855733
Security problems were just a possibility; if you're running as an admin and everything's local, security is probably not an issue.

Putting the DLL in the directory of the spreadsheet will not work since it has to be local to the executable.  One other option is to use the /codebase option of REGASM to tell .NET where to find the assembly:

Option  Description  
/codebase
 Creates a Codebase entry in the registry. The Codebase entry specifies the file path for an assembly that is not installed in the global assembly cache. You should not specify this option if you will subsequently install the assembly that you are registering into the global assembly cache. The assemblyFile argument that you specify with the /codebase option must be a strong-named assembly.
 
Here are some Google results that might help you out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=445170&SiteID=1
http://www.dotnet247.com/247reference/msgs/49/245264.aspx

Also, try not using the /tlb option with regasm.  You don't need it since you can reference the DLL directly from Excel.
0
 

Author Comment

by:Fledgewing
ID: 16857215
dstanley9,

I tried with the two links, and the codebase, and figure out what the problem was.  I haven't installed it in the GAC, just in a directory, and it needs to create TLBs, and I used the codebase option (I'm unable to use it through Excel at all without the TLB).  I added a reference to this (http://support.microsoft.com/kb/908002/, see download), and everything worked perfectly from my install!  I managed to get it to the point where I got an error that I recognized (I've used that KB download before to fix an Automation error), and fixed that with the download.

Thanks for the help!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question