Execute .NET 3.0 code from Office 2003

Question

I've created a DLL in C# using the .NET 3.0 framework.

Below is the code of my DLL

namespace CompanyName.Net
{
    [Guid("F7075E8D-A6BD-4590-A3B5-7728C94E372F")]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ProgId("CompanyName.Net.Webrequest")]
    public class WebRequest
    {
        public string Result { get; private set; }
        public string Url { get; set; }
        public string StatusDescription { get; private set; }
        public HttpStatusCode StatusCode { get; private set; }

        public WebRequest()
        {
            //explicit constructor
        }    

        public string GetResponse(string url)
        {
            System.Net.WebRequest webreq = System.Net.WebRequest.Create(url);
            HttpWebResponse response = (HttpWebResponse) webreq.GetResponse();
            // Store the status.
            StatusDescription = response.StatusDescription;
            StatusCode = response.StatusCode;
            // Get the stream containing content returned by the server.
            Stream dataStream = response.GetResponseStream();
            // Open the stream using a StreamReader for easy access.
            StreamReader reader = new StreamReader(dataStream);
            // Read the content.
            Result = reader.ReadToEnd();
            // Cleanup the streams and the response.
            reader.Close();
            dataStream.Close();
            response.Close();
            //return the response
            return Result;
        }
    }
}

I'm trying to get this code to run from Office 2003 VBA code. The DLL was signed using the default Visual Studio 2008 signing.

I've managed to reference my assembly by creating a .TLB file using

regasm /tlb c:\CompanyName.Net.dll

But when I want to create an instance of the object:

Private Sub Command0_Click()
    Dim o As Object
    Set o = CreateObject("CompanyName.Net.WebRequest")
    Dim s As String
    s = o.GetResponse("http://www.google.be")
    MsgBox s
End Sub

I get the following error:

ActiveX component can't create Object

What am I doing wrong?

The machine I'm testing with has .NET installed up to the .NET 3.5 framework.

Answer 1

You need an explicit parameterless constructor in your COM class. Change your class definition to:

namespace CompanyName.Net
{
    [Guid("F7075E8D-A6BD-4590-A3B5-7728C94E372F")]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ProgId("CompanyName.Net.Webrequest")]
    public class WebRequest
    {
        public string Result { get; private set; }
        public string Url { get; set; }
        public string StatusDescription { get; private set; }
        public HttpStatusCode StatusCode { get; private set; }

        public WebRequest()
        {
        }

        public string GetResponse(string url)
        {
            System.Net.WebRequest webreq = System.Net.WebRequest.Create(url);
            HttpWebResponse response = (HttpWebResponse) webreq.GetResponse();
            // Store the status.
            StatusDescription = response.StatusDescription;
            StatusCode = response.StatusCode;
            // Get the stream containing content returned by the server.
            Stream dataStream = response.GetResponseStream();
            // Open the stream using a StreamReader for easy access.
            StreamReader reader = new StreamReader(dataStream);
            // Read the content.
            Result = reader.ReadToEnd();
            // Cleanup the streams and the response.
            reader.Close();
            dataStream.Close();
            response.Close();
            //return the response
            return Result;
        }
    }
}

This should work.

Answer 2

OK, after some pretty good ideas from Thorsten Dittmar I finally got this thing to work. Some things that came up during our discussion and other things I found on the web:

1. The .NET framework needs to be installed on the target machine.
2. .Net Programmability support needs to be installed on the target machine.

3. In AssemblyInfo.cs make sure you set

   [assembly: ComVisible(true)]

4. As Thorsten pointed out you need to have parameterless public constructor in your .Net class.

5. Make sure to check "Register for COM Interop" in the Build tab on the Properties page of your project.
6. Make sure to sign your project using the Signing tab on the Properties page of your project.

7. Register your DLL on the target machine by running this command. The /codebase parameter seemed to do the trick for me. The path to the type library (.tlb) or DLL doesn't matter. You can find regasm at C:\Windows\Microsoft.Net\Framework\v2.050727\RegAsm.exe

   regasm c:\CompanyName.Net.dll /tlb:CompanyName.Net.tlb /codebase

8. Reference the .tlb file in your VBA Editor using Tools > References.

9. Drag the dll from C:\ to your GAC at C:\Windows\assembly\ (I didn't realize this at first but apparently it is necessary for Office to find your assembly.)

That should do the trick.

I also upgraded my Webrequest class by adding an interface for it thus enabled IntelliSense support in VB6 (which sadly enough does not work in VBA).