Calvin Hsia's WebLog

Create a .Net UserControl that calls a web service that acts as an ActiveX control to use in Excel, VB6, Foxpro

Here’s how you can use Visual Studio to create a .Net User Control that will act as an ActiveX control that can be used in Excel (or other Office application), VB6 or Foxpro. This will bring the power of the entire.Net Framework to your program. This sample uses a Web Service to look up zip codes.

I browsed some web services available at www.xmethods.net and found one that returns information on a zip code. (You can also try a MapPoint Web Service: http://msdn.microsoft.com/mappoint/default.aspx?pull=/library/en-us/dnmapnet30/html/MPWS_AND.asp)

Start Visual Studio 2005, choose File->New->Project->VB Windows Class Library. Call it VBZipWebService.

From the Solution Explorer, delete Class1.vb, then right click on the VBZipWebService project (not the VBZipWebService solution), choose Add New Item, choose User Control, call it VBZipWebService.vb

From Project->Properties->Application, make sure the Root Namespace is VBZipWebService and not ClassLibrary1. (If you don’t the ProgId for the control will be ClassLibrary1.VBZipWebService). Now the ProgId will be VBZipWebService.VBZipWebService

You just created a control with a design surface. Add a Button and a DataGridView.

Right click the project again, choose Add Web Reference, then paste in this URL: http://www.jasongaylord.com/webservices/zipcodes.asmx?wsdl as the WSDL (Web Service Description Language), Click Go to see the Web methods, then Add Reference. Note that the “Web Reference Name” is “com.jasongaylord.www”

Paste in this code:

Public Class VBZipWebService

    Dim _Zipcode As String = "98052"

    Public Property ZipCode() As String

        Get

            Return _Zipcode

        End Get

        Set(ByVal Value As String)

            _Zipcode = Value

        End Set

    End Property

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim ozip As New com.jasongaylord.www.ZipCodes

        Dim ds As Data.DataSet = ozip.ZipCodeToDetails(Me._Zipcode)

        Me.DataGridView1.AutoGenerateColumns = True

        Me.DataGridView1.DataSource = ds.Tables(0)

        Me.DataGridView1.AutoResizeColumns()

    End Sub

End Class

Hit the F5 button to test your UserControl in the UserControl TestContainer. Click the button and wait a few seconds for the se

Now that you have it working the way you want, let’s make this UserControl act like a normal ActiveX control that can be used from Foxpro, Excel, VB6.

Choose Project->Properties->Compile tab. Make sure “Register For COM interop” checkbox is checked.

Choose Project->Add New Item->Module. Call it Comregistration.vb, and paste this code into it:

Imports Microsoft.Win32

Imports System.Windows.Forms

Friend Module ComRegistration

    Public Sub RegisterControl(ByVal t As Type)

        Try

            GuardNullType(t, "t")

            GuardTypeIsControl(t)

            ' CLSID

            Dim key As String = "CLSID\" & t.GUID.ToString("B")

            Using subkey As RegistryKey = Registry.ClassesRoot.OpenSubKey(key, True)

                ' Control

                Using controlKey As RegistryKey = subkey.CreateSubKey("Control")

                End Using

                ' Misc

                Using miscKey As RegistryKey = subkey.CreateSubKey("MiscStatus")

                    miscKey.SetValue("", "131457", RegistryValueKind.String)

                End Using

                ' TypeLib

                Using typeLibKey As RegistryKey = subkey.CreateSubKey("TypeLib")

                    Dim libId As Guid = System.Runtime.InteropServices.Marshal.GetTypeLibGuidForAssembly(t.Assembly)

                    typeLibKey.SetValue("", libId.ToString("B"), RegistryValueKind.String)

                End Using

                ' Version

                Using versionKey As RegistryKey = subkey.CreateSubKey("Version")

                    Dim major, minor As Integer

                    System.Runtime.InteropServices.Marshal.GetTypeLibVersionForAssembly(t.Assembly, major, minor)

                    versionKey.SetValue("", String.Format("{0}.{1}", major, minor))

                End Using

            End Using

        Catch ex As Exception

            HandleException("ComRegisterFunction failed.", t, ex)

        End Try

    End Sub

    Public Sub UnregisterControl(ByVal t As Type)

        Try

            GuardNullType(t, "t")

            GuardTypeIsControl(t)

            ' CLSID

            Dim key As String = "CLSID\" & t.GUID.ToString("B")

            Registry.ClassesRoot.DeleteSubKeyTree(key)

        Catch ex As Exception

            HandleException("ComUnregisterFunction failed.", t, ex)

        End Try

    End Sub

    Private Sub GuardNullType(ByVal t As Type, ByVal param As String)

        If t Is Nothing Then

            Throw New ArgumentException("The CLR type must be specified.", param)

        End If

    End Sub

    Private Sub GuardTypeIsControl(ByVal t As Type)

        If Not GetType(Control).IsAssignableFrom(t) Then

            Throw New ArgumentException("Type argument must be a Windows Forms control.")

        End If

    End Sub

    Private Sub HandleException(ByVal message As String, ByVal t As Type, ByVal ex As Exception)

        Try

            If t IsNot Nothing Then

                message &= vbCrLf & String.Format("CLR class '{0}'", t.FullName)

            End If

            Throw New Exception(message, ex) ' replace with custom exception type

        Catch ex2 As Exception

            My.Application.Log.WriteException(ex2)

        End Try

    End Sub

End Module

As you can see, this code has a couple methods to add a few registry keys on Registering and Unregistering. Now we need to have this code called when the assembly is registered or unregistered. The ComRegisterFunctionAttribute class helps here. (ComRegistration.vb is reusable as is with all your controls) When you build, the assembly is automatically registered and these methods are called.

We add 2 methods to use these attributes, an Imports statement, and a Public Event to show how we can get events from the control.

We also add an attribute to your Class line

<Microsoft.VisualBasic.ComClass()> Public Class VBZipWebService

(Alternatively, you can move your cursor to that line and double click the property sheet Com Class property to make it True. Also, make sure the Project->Properties->Compile->Register for COM Interop checkbox is checked.)

Now your code looks like this:

Imports System.Runtime.InteropServices

<Microsoft.VisualBasic.ComClass()> Public Class VBZipWebService

    Public Event GotData(ByVal City As String)

    Dim _Zipcode As String = "98052"

    Public Property ZipCode() As String

        Get

            Return _Zipcode

        End Get

        Set(ByVal Value As String)

            _Zipcode = Value

        End Set

    End Property

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim ozip As New com.jasongaylord.www.ZipCodes

        Dim ds As Data.DataSet = ozip.ZipCodeToDetails(Me._Zipcode)

        Me.DataGridView1.AutoGenerateColumns = True

        Me.DataGridView1.DataSource = ds.Tables(0)

        Me.DataGridView1.AutoResizeColumns()

        RaiseEvent GotData(ds.Tables(0).Rows(0).Item("city"))

    End Sub

    <ComRegisterFunction()> _

    Public Shared Sub Register(ByVal t As Type)

        ComRegistration.RegisterControl(t)

    End Sub

    <ComUnregisterFunction()> _

    Public Shared Sub Unregister(ByVal t As Type)

        ComRegistration.UnregisterControl(t)

    End Sub

End Class

Hit F5 to build and see that it works as before.

From Excel Hit Alt-F11 (Tools->Macros->Visual Basic Editor), then Insert->User Form.

From VB6 and Excel, right click on the toolbar and choose “Additional Controls”(Excel) or “Components…”(VB6). Drag the control onto the form and hit F5

You can also handle the event by adding this code in Excel:

Private Sub VBZipWebService1_GotData(ByVal City As String)

MsgBox ("got data " & City)

End Sub

From Foxpro, run this code:

PUBLIC ox

ox=CREATEOBJECT("myform")

ox.show

DEFINE CLASS myform AS form

          left=300

          allowoutput=.f.

          width=400

          height=300

          PROCEDURE Init

                   this.AddObject("ocZip","myoc")

                   this.ocZip.width=thisform.Width

                   this.ocZip.height=thisform.Height

                   this.ocZip.visible=1

ENDDEFINE

DEFINE CLASS myoc AS olecontrol

          oleclass="VBZipWebService.VBZipWebService"

          PROCEDURE init

                   this.object.zipcode="96825"

          PROCEDURE Gotdata(p1,p2)

                   ?PROGRAM(),p1

ENDDEFINE

Alternatively, Foxpro Tools->Options->Controls->ActiveX Controls will allow you to register any ActiveX control so it shows up on the Form Designer toolbar.

Additional notes:

The Event can pass a .Net object as a parameter: Just add an attribute:

Public Event DocObjectUnknown(<MarshalAs(UnmanagedType.IUnknown)> ByVal sender As Object)

That way the client gets an object reference to the .Net object (which may be different from the user control).

To rebuild the control, you have to close the client process (Fox, Excel, VB6) to unload the CLR.

Click the Solution Explorer->Show All Files button and expand the web reference and look at some of the generated files.

(Thanks to PaulYuk and AbelV for their help)

See also:

Use Regular Expressions to get hyperlinks in blogs

I just finished composing this blog entry and I found Craig Boyd’s sample.