Partager via

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

  • Article

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: https://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: https://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:

A Visual Basic COM object is simple to create, call and debug from Excel

Use Regular Expressions to get hyperlinks in blogs

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

Comments

  • Anonymous
    July 18, 2006
    My prior post (Create a .Net UserControl that calls a web service that acts as an ActiveX control to...

  • Anonymous
    July 18, 2006
    PingBack from http://microsoft.wagalulu.com/2006/07/18/use-a-different-kind-of-grid-in-your-applications/

  • Anonymous
    July 18, 2006
    Hmm...last time I heard, only IE and MFC7 are supported ActiveX control containers for .NET controls.

    http://support.microsoft.com/kb/311334/en-us

  • Anonymous
    July 19, 2006
    True, VB6 is not a supported ActiveX control container for .NET User Controls today.  However, Calvin and I are working together to see if it is possible longer term to add this as a MS Supported container.  Meanwhile, guidance like this helps you understand the current limitations and navigate around them (unsupported).

    Paul Yuknewicz (Paulyuk)
    VB Program Manager

  • Anonymous
    July 31, 2006
    I have tried your example.. It works fine in Excel. But in VB6 the event doesn't raise. Any clue?

    Regards,
    Joris

  • Anonymous
    August 02, 2006
    Yes Joris, a .Net control will fire events fine in Excel and Foxpro, but not in VB6. This is a known issue, currently being investigated.

  • Anonymous
    August 07, 2006
    Here’s some C++ code to host the CLR. It’s an alternative to using COM Interop (see A Visual Basic COM...

  • Anonymous
    August 28, 2006
    Creating an ActiveX control is a good exercise in understanding how one works. It also helps to have...

  • Anonymous
    October 20, 2006
    can this same method be used for embedding the activex in a browser?

  • Anonymous
    November 23, 2006
    I have tried your example.. It works fine in VB6 but on this usercontrol in VB6 I don't have any  events (like Click, MouseDown,MouseUp)  only DragDrop,DragOver, LostFocus,GotFocus and Validate events. Do you have any clue about this? Thx Avy

  • Anonymous
    November 23, 2006
    How can I export a  c#.Net object as an ocx/activex/ecc.. to use in old style unmanaged application ( the old c++ builder5 or other c++ )I would like to use a procedure that doesen't use VB... do you have some idea? Thx R.

  • Anonymous
    January 09, 2007
    The comment has been removed

  • Anonymous
    February 18, 2007
    This is a great example! thanks. It's all working nicely on my development machine. Does anyone have any experience with deploying a VB6 app that uses a .net control?  

  • Anonymous
    February 28, 2007
    How to Debug VB6 Activex Usercontrol from VB.Net

  • Anonymous
    May 03, 2007
    hello, In my case, this works fine in tstcon.exe but not in excel, (maybe I missed some thing). I can add this activex in vba editor "additional controls" but when I add to UserForm1, there was an error: the system cannot find the file specified. Moreover the control icon in toolBox has "Unknown" as tooltipBox ,Hope I have a reply!!

  • Anonymous
    October 09, 2007
    The comment has been removed

  • Anonymous
    December 12, 2007
    My prior post showed how to create XAML WPF and put it on your Winform App. We can go one step further:

  • Anonymous
    December 12, 2007
    My prior post showed how to create XAML WPF and put it on your Winform App. We can go one step further

  • Anonymous
    April 08, 2008
    Hi All, I have created user control in vb.net but it does not handle events like mouseup,mousedown. Please reply as soon as possible. Thanx

  • Anonymous
    May 22, 2008
    Hi All can you help me to invoke this control from html sample       <OBJECT id="myControl1" name="myControl1" classid="VBZipWebService.dll#VBZipWebService.VBZipWebService" width=288 height=72>       </OBJECT> this don't work

  • Anonymous
    July 01, 2008
    Thank you so much for this article.  I'm starting with VB.Net and this article answered some questions I had.

  • Anonymous
    October 28, 2008
    When running VB.Net or C# code, often it’s useful to call native code. One way is using PInvoke. (for

  • Anonymous
    September 28, 2009
    Hi, I've a dot net user control (vb.net 2005) created using Interop toolkit. It displays video using a IIIrd party control. So far so good, but as soon as I use it in VB6, control is not rendered, it is there as I can display its properties on runtime and other controls like panel and buttons are there. But somehow vidoe is not displayed. I'm not sure if someone faced this particular problem before. Any help or guidance is greatly appreciated. Thanks

  • Anonymous
    January 25, 2015
    I make a usercontrol with C#, but i can't add it to foxpro. I can create this object in Foxpro, but When I do  Foxpro Tools->Options->Controls->ActiveX Controls, it can't register, anyone has ths same problem?

  • Anonymous
    April 27, 2015
    (O.O)/ When i try to register the control it says Was not found an entry point DllRegisterServer Help me!!!!!!!!!!!!!