STEP BY STEP: Consuming Web Services through VBA (Excel or Word) - Part I

Many of you would have been comfortable by now of consuming web services from a .NET client (and that is straight forward too!!). It’s just like accessing another object sitting at your place. But consuming web services from an Excel or Word application using VBA is a different scenario all together. You would need to install something and you would need to tweak something in the code for this to work.

What do you need?

Well, for this to work, you should install the Office Web Services Toolkit from the Microsoft's site. You can find it here -

You have to download the offwstk.msi (the name can be changed in future) and you have to run this once. Once you have done that, you have to navigate to your Start -> Programs -> Office Web Services Toolkit and click on the Office Web Services Overview.
On the left pane, click on the “Web Services References Tool"
Click on the Setup.exe link to run the setup. Just go ahead with the default values and the installation takes just seconds to complete.

How do you know that it is installed?

Well, you can test this out by opening an Excel workbook. Then go to Tools -> Macro -> Visual Basic Editor. Now, click on Tools menu again, you will see the Web Services References link.
Start consuming – Web Services first…

Now that we got the things ready, let's start moving on. Create a sample web service – a simple web method, which returns a string (“Hello World!") would suffice. As you are going to see later on, we will look into complex types also. But as of now, it is just the traditional opening ceremony – Hello World!!

Let's fire a new Web Services project and name it as SampleService. So your Web Service class is SampleService and your asmx file will be SampleService.asmx. Write the following web method inside the class: (All code samples are in VB.NET)

<WebMethod()> _
Public Function HelloWorld(ByVal Name as String) as String
Return “Wishing “ & Name & “, Hello World!!"
End Function

Compile the class and your web service is ready.

VBA Next (and last, of course!!)

Its time to check the web services on the other side – your Excel and VBA code. And we start right away. Open up an Excel workbook and name it as whatever you want. To go to the Visual Basic editor go to Tools -> Macro -> Visual Basic Editor. Click on Tools (again) -> Web Service References. You will see a dialog box.

Select the Web Service URL option and provide the complete asmx URL. Click on search and on the Search Results pane, you will find one result – SampleService. Select that and click on Add button to close the dialog box.

Once you do that, you will see that a new Class Module is added with the name, clsws_SampleService.
We will look at this class module later. But as of now, we are going to reference this class for calling our web method. Now, for testing our code, select the ThisWorkbook object from the project explorer and we are going to write our VBA code on the workbook's Open event. (There is only one event for the workbook, which is Open).

This is the VBA code that will wish you (or whoever depending upon the value that you passed to the parameter), Hello World!!

Private Sub Workbook_Open()

On Error GoTo errhandler

Dim objWS As New clsws_SampleService
Dim strOutput As String

strOutput = objWS.wsm_HelloWorld("Gopal")
MsgBox strOutput

Exit Sub
MsgBox Err.Description, vbCritical

End Sub

And you will get the output.
Got it, but things you should know

Congratulations and you have just made your web service work for you. But there are a few things that you should know in the above code. First, look at the name of the class. It is clsws_SampleService, where “cls" means Class and “ws" means Web Service. And look at the Web Method. All the web methods will be prefixed by wsm (wsm_HelloWorld), where “wsm" means Web Service Method.

As you can see, once you install this toolkit, it is mostly straight forward job to call and execute a Web Service. Now, we still have 2 more things to do. One is about returning Complex Types. Generally, you would expect some data from a RDBMS data store (like MS SQL Server) to be populated in Excel sheet. And the Web Service will return a DataSet object, which may contains all those rows. How do we program this? The other thing is we need to just look at the class module generated by the Web Services Toolkit and understand something about what it is and what it does. All these things, we will look into it in the coming days.

Bye, Bye!!


Author: raveena uppar18 Jul 2004 Member Level: Silver   Points : 0

Its really nice infn,i didn't had a clue abt this information
thanks,keep posting these kind od infn

Guest Author: 22 May 2012

Great article, but it's a shame I can't see any images in my browsers (Firefox 12, IE 9, Google Chrome 19, Safari 5).

Guest Author: William29 Aug 2014

Unfortunately, WebServices toolkit no longer works in Excel. It can be installed, but can't be run because it requires Internet Explorer 5.01 or higher (I have IE 11, and it's too stupid to know).

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: