Click here to Skip to main content
12,241,564 members (47,869 online)
Click here to Skip to main content
Add your own
alternative version

Stats

125.5K views
1.1K downloads
44 bookmarked
Posted

Consuming Web Services from MS Excel

, 31 Aug 2003
Rate this:
Please Sign up or sign in to vote.
How to access a web service from Microsoft Excel

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

 Sample screenshot

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.

Sample screenshot

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

License

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

Share

About the Author

Kodanda Pani
Web Developer
India India
Working on .NET for last 6 years. Currently working for TCS.

You may also be interested in...

Comments and Discussions

 
QuestionLicense Pin
marco.gatto1-Nov-12 23:23
membermarco.gatto1-Nov-12 23:23 
GeneralMy vote of 1 Pin
elomer28-Jul-10 6:00
memberelomer28-Jul-10 6:00 
GeneralRe: My vote of 1 Pin
Kodanda Pani29-Dec-10 23:57
memberKodanda Pani29-Dec-10 23:57 
GeneralWeb Service Toolkit Error Pin
smud13-Nov-06 15:05
membersmud13-Nov-06 15:05 
GeneralWeb services in Excel w/out programming Pin
dipskinny25-Sep-06 19:39
memberdipskinny25-Sep-06 19:39 
GeneralSetting up the Webservices Pin
gjenkin4-May-05 3:53
membergjenkin4-May-05 3:53 
GeneralRe: Setting up the Webservices Pin
Kodanda Pani4-May-05 19:36
memberKodanda Pani4-May-05 19:36 
GeneralRe: Setting up the Webservices Pin
gjenkin10-May-05 3:53
membergjenkin10-May-05 3:53 
GeneralRe: Setting up the Webservices.(Help Needed Immediately....) Pin
user3527-Sep-05 4:44
sussuser3527-Sep-05 4:44 
GeneralRe: Setting up the Webservices.(Help Needed Immediately....) Pin
takaka5-Nov-06 18:28
membertakaka5-Nov-06 18:28 
QuestionSOAP Tool Kit? Pin
rball31-Mar-05 8:30
memberrball31-Mar-05 8:30 
AnswerRe: SOAP Tool Kit? Pin
Kodanda Pani3-Apr-05 20:17
memberKodanda Pani3-Apr-05 20:17 
AnswerRe: SOAP Tool Kit? Pin
gjenkin4-May-05 3:50
membergjenkin4-May-05 3:50 
GeneralRe: SOAP Tool Kit? Pin
chethana rao7-Apr-06 21:00
memberchethana rao7-Apr-06 21:00 
GeneralRe: SOAP Tool Kit? Pin
ChiefOdie24-Oct-06 12:46
memberChiefOdie24-Oct-06 12:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 1 Sep 2003
Article Copyright 2003 by Kodanda Pani
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid