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:


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 (25)

  1. My prior post (Create a .Net UserControl that calls a web service that acts as an ActiveX control to…

  2. Kevin Dente says:

    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

  3. VBTeam says:

    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

  4. Joris Schoot says:

    I have tried your example.. It works fine in Excel. But in VB6 the event doesn’t raise. Any clue?

    Regards,

    Joris

  5. Calvin_Hsia says:

    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.

  6. Here’s some C++ code to host the CLR. It’s an alternative to using COM Interop (see A Visual Basic COM…

  7. Creating an ActiveX control is a good exercise in understanding how one works. It also helps to have…

  8. Jeff says:

    can this same method be used for embedding the activex in a browser?

  9. Avy says:

    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

  10. Rubio says:

    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.

  11. Avy says:

    Hi again.

    I have another usercontrol problem.

    I make a usercontrol(that inherits usercontrol class) in VB.net 2005 that contains a toolstrip control and on that toolstrip I add in runtime  9 buttons.

    I make this usercontrol as tlb and I try to add in VB 6 as components and it give me an error: "Name conflicts with existing module, project, or object library"

    Do you have any solution for that?

    When I develop a usercontrol that inherits button class and not usercontrol class it is working.I think this is a problem but I am not so sure.

    Could anyone help me with that, please?

    Thks

    Avy

  12. David says:

    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?  

  13. Manoharan says:

    How to Debug VB6 Activex Usercontrol from VB.Net

  14. med111 says:

    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!!

  15. Injuninus says:

    When I Rebuild the .NET Assembly, the reference in VB6 breaks. I get an exception "Object library invalid or contains references to object definitions that could not be found".

  16. My prior post showed how to create XAML WPF and put it on your Winform App. We can go one step further:

  17. My prior post showed how to create XAML WPF and put it on your Winform App. We can go one step further

  18. prajakta says:

    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

  19. Beto says:

    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

  20. hank says:

    Thank you so much for this article.  I’m starting with VB.Net and this article answered some questions I had.

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

  22. Nitin says:

    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

  23. Michael says:

    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?

  24. Sergo Barranco says:

    (O.O)/

    When i try to register the control it says

    Was not found an entry point DllRegisterServer

    Help me!!!!!!!!!!!!!