Consuming Web Services from MS Excel

• Download source files - 30.4 Kb

Introduction

Web Services are reusable applications that expose an interface that can be accessed over the internet. Web services use XML to provide a platform and language independent implementation of distributed application services and information.

Web services allow us to utilize the services provided by the other organizations. We can consume the web services not only from applications developed using various programming languages but also from Microsoft office tools like Access, Excel, and Word etc. In this article we will explore how we can access a web service from Microsoft Excel using Microsoft Office XP Web Services Toolkit 2.0, which simplifies the process of accessing and utilizing an XML webservice.

Details

The example includes a webservice, which returns 5 countries and their capitals. Capitals.xls has 2 buttons. When a user clicks on the display button, Capitals.xls sheet accesses the web service and displays the data provided by the webservice. Clicking on the clear button clears the data from the XL sheet.

Now lets see how we can develop this application. Lets start by creating an excel sheet. Drag and drop a button from the visual basic toolbox. (You can get the VB toolbox from the View menu). Now to add a reference to the web service . For this we will use the VB editor (Alt-F11). From the tools menu bar add the reference of Microsoft Soap tool kit. Now lets add the web service reference. For this click on the web reference in the tools menu of the VB editor. You can either search for a web service or if you know the URL of a web service u can directly add it. Now you are ready to access the web service. We can do this by creating an object of the web service class and displaying the result in the excel sheet.

Dim module As clsws_Service1
Dim value As String
Dim data() As String
Dim range1 As Range
Dim range2 As Range
Dim j As Integer
Set module = New clsws_Service1
value = module.wsm_getCapitals()
data = Split(value)
j = 0
Application.ActiveSheet.Range("A4").Activate
Set range1 = Range(Range("A4"), Range("A22"))
For Each range2 In range1
    If j < 9 Then
     range2.Offset(0, 3).value = data(j)
     range2.Offset(0, 4).value = data(j + 1)
     j = j + 2
   End If
Next range2

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here