My Profile
Gifts
Active Members
TodayLast 7 Days
more...
|
STEP BY STEP: Consuming Web Services through VBA (Excel or Word) - Part I
Posted Date: 06 Feb 2004 Resource Type: Articles Category: Webservices
|
Posted By: N.T.Gopalakrishnan Member Level: Gold
Rating:  Points: 10
|
The resource has not been reviewed by Editors yet. Readers are adviced to use their best judgement before accessing this resource.
This resource will be reviewed shortly.
If you think this resource contain inappropriate content, please report to webmaster. |
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 -
http://msdn.microsoft.com/office/downloads/toolsutils/default.aspx
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. You will see this window:
On the left pane, click on the “Web Services References Tool” and you will see this window now:
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 (see picture below):
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 the following 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 (Look at the picture below):
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
errhandler:
MsgBox Err.Description, vbCritical
End Sub
And you will see this 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!!
|
Responses
|
| Author: raveena uppar 18 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
|
|