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: 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.