I registered a COM DLL using regasm.exe, and now I'm trying to write a VBA script that uses a class from the DLL. The DLL is ExcelDataReaderLibrary.dll. In the C# source, the class is described as follows (includes code from this library):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data;
using Excel;
namespace ExcelDataReaderLibrary
{
public class ExcelDataReader
{
public void readSheet(string filePath,string sheetName,string outPath)
{
// code for method here
}
}
}
My assembly.cs file includes the following:
[assembly: ComVisible(true)]
[assembly: Guid("b1e78f8f-9ab0-46d8-beac-b843656aacdb")]
When I open the VBA Editor and go to References, I see a reference for ExcelDataReaderLibrary. Note that the file associated with this reference is ExcelDataReaderLibrary.tlb, not ExcelDataReaderLibrary.dll. After I check this reference, I want to create and use an ExcelDataReader object in VBA as follows:
Sub x()
Dim xyz As New ExcelDataReaderLibrary.ExcelDataReader
xyz.readSheet "c:\mypath\testfile.xlsx", "Sheet1", "c:\outputPath"
End Sub
The object is successfully created, but readSheet gives this error:
Automation error
The system cannot find the file specified.
Also, there is Intellisense for the ExcelDataReaderLibrary namespace, but there is no Intellisense for the ExcelDataReader object. I guess my class is registered but not its method--do I have to do something different with the Guid? How can I call the method from my VBA code?
For the first part of your question, the problem is that mscoree.dll needs to find your assembly from the location/context of the calling process, and your object's assembly is not usually in a folder that your process (technically, .NET fusion) will look in. To tell .NET where to find the assembly, you have two options:
Use the /codebase parameter of REGASM (as Hans mentioned), which leaves a hint on the registry that points to the assembly's location; or
Strong-sign your assembly and add it to the GAC, where mscoree.dll will always look and find it.
Notice that all the dependencies of your assembly must be equally "findable".
I must warn you that Microsoft very sternly discourages people from using the /codebase technique. They argue that it is designed as a development/debugging technique and that it should not be used in production mode. I'm not sure I fully understand their rationale. You should probably look into the documentation for REGASM and other references in MSDN and make up your own mind about this. I don't personally find adding my objects to the GAC any more onerous than using /codebase.
On the second part of your question, about not getting IntelliSense: your problem is that, by default, COM type libraries generated by .NET expose pure dispinterfaces (they implement only IDispatch, and the type library won't even list the dispinterface members). In VB6 terminology, your classes are exposed as objects, and all method names and parameters must be determined at run-time.
The generation of the COM class is controlled by an attribute applied to your class, called ClassInterfaceAttribute.
Here are the options:
ClassInterfaceType.AutoDispatch: This is the default and so it applies currently to your class. Your class is exposed as a pure dispinterface, and can only be late-bound. No IntelliSense. The TLB doesn't even list the dispinterface members, so a late-bound client cannot ever cache any details about what your class exposes.
This allows maximum flexibility to add, change and remove members with impunity, at least insofar as not hard-crashing the client is concerned. I call this "scripting mode".
ClassInterfaceType.None: This is the stricter mode, that asks you to expose your object more explicitly, kind of like you would have done in IDL/C++. You are expected to declare one of more interfaces with the methods that you want to expose, and make the class explicitly inherit from the interface(s). If you inherit from more than one interface, the first one will be picked as the [default] interface, but you should probably designate one explicitly via the ComDefaultInterfaceAttribute. If you don't inherit from any interface, your CoClass will inherit straight from IDispatch. (All .NET classes exposed as COM objects are exposed via IDispatch).
This is my preferred mode, but I'm more of a traditionalist when it comes to COM programming. You do get IntelliSense, as long as you do declare and inherit explicitly from an Interface. Obviously, you only get to call the members listed in the Interface. I call this "Strict mode" or "IDL mode".
ClassInterfaceType.AutoDual This produces a COM interface automatically for you, one that includes all the details of the methods you are exposing. That means you get IntelliSense and you don't have to worry about creating an explicit interface. However, versioning is a royal pain. You have to be very careful to stop all clients before recompiling and/or re-registering your object, or you will be in a world of hurt if your method signatures changed in any way. I call this "VB6 mode", because to me it looks a lot like what VB6 did for you (COM versioning on VB6 is also a royal pain).
Microsoft also strongly discourages people from using AutoDual, I suppose because changes to the generated interface can happen more easily without you noticing. I haven't actually had a chance to use it yet, but I'm not sure that it is that much more dangerous than None.
In summary: If you want to get IntelliSense, you need to apply to your class either [ClassInterface(ClassInterfaceType.None)] (and put your methods in an interface that you explicitly implement), or [ClassInterface(ClassInterfaceType.AutoDual)]. Either way, you have to be very careful to stop your clients (and maybe even "removing" and "re-adding" the references) before making changes to your assembly.
