Archive for the ‘VB Scripting’ Category

Call WCF service from Excel

October 13, 2014 Leave a comment

We can call a WCF service from the excel work sheet as client.

First construct a WCF service by creating a WCF Service Library from Visual Studio 2012(am using this version of VS).

Am using the same test template provided by the Visual Studio


Remove all composite type stuffs from the source files Service1.cs and IService1.cs (we don’t need for now)

So the new files will look like this

A small change I made was the input parameter changed to string.

3      4

Next main thing is App.config.

You can use http or tcp as service protocol. I described both in App.config (tcp configuration is commented)

<?xml version="1.0" encoding="utf-8" ?>

 <compilation debug="true" />
 <!-- When deploying the service library project, the content of the config file must be added to the host's 
 app.config file. System.Configuration does not support config files for libraries. -->
 <service behaviorConfiguration="MyTestWcfService.Service1Behavior" name="MyTestWcfService.Service1">

 <endpoint address="" binding="basicHttpBinding" bindingConfiguration=""
 <dns value="localhost" />

 <endpoint address="mex" binding="mexHttpBinding" bindingConfiguration=""
 contract="IMetadataExchange" />

 <!--<endpoint address="" binding="netTcpBinding" bindingConfiguration=""
 <dns value="localhost" />
 <endpoint address="mex" binding="mexTcpBinding" bindingConfiguration=""
 contract="IMetadataExchange" />-->
 <!--<add baseAddress="net.tcp://localhost:8733/Test/MyTestWcfService/Service1/" />-->
 <add baseAddress="http://localhost:8733/Test/MyTestWcfService/Service1/" />
 <behavior name="MyTestWcfService.Service1Behavior">
 <serviceMetadata httpGetEnabled="false" />
 <serviceDebug includeExceptionDetailInFaults="false" />


Run the service


You have done with the service end.

Lets start the Excel and press Alt + F11. You can see something like this


Double click sheet1 from the left side.

From the new window. Select Worksheet from the first drop down and Select change from the second drop down.


Remove Worksheet_SelectionChange function definition as we don’t need in this demo.

Private Sub Worksheet_Change(ByVal x As Range)
Dim addr As String
addr = "service:mexAddress=""http://localhost:8733/Test/MyTestWcfService/Service1/mex"","
addr = addr + "address=""http://localhost:8733/Test/MyTestWcfService/Service1/"","
addr = addr + "contract=""IService1"", contractNamespace="""","
addr = addr + "binding=""BasicHttpBinding_IService1"", bindingNamespace="""""

Dim service1 As Object
Set service1 = GetObject(addr)
Dim text As String
text = Cells.Item(x.Row, x.Column)
MsgBox service1.GetData(text)

End Sub

We can get the service object as service1 by calling GetObject(addr).

The above address configuration is for http protocol. For tcp please use this address configuration (Uncomment the tcp stuffs from the service too if you are using tcp)

addr = "service:mexAddress=""net.tcp://localhost:8733/Test/MyTestWcfService/Service1/mex"","
addr = addr + "address=""net.tcp://localhost:8733/Test/MyTestWcfService/Service1/"","
addr = addr + "contract=""IService1"", contractNamespace="""","
addr = addr + "binding=""NetTcpBinding_IService1"", bindingNamespace="""""

You are done. The service accept the value typed in a cell and show it in Message Box.

Save the document as macro enabled document (Book1.xlsm)

You can see the the output as


Thanks for reading.

Happy coding 🙂