Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
|
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
Resources » Articles » WCF/Webservices »
STEP BY STEP: Consuming Webservices through VBA (Excel or Word) - Part II
|
What are Complex Types?
Anything which is not intrinsic is called complex type. That is, intrinsic values like string, integer or Boolean can be directly consumed by the Web Service Toolkit. But any “object” – whether it is created by you or you are referencing some library, are called complex types.
How this is going to get referenced here?
Well, the answer is that all complex types are returned as XMLNodeList object. Each XMLNode object inside that is a row from your table and the child nodes under the XMLNode object are columns from your table. You return a DataSet from your web service, but I can see it only as an XMLNodeList object at the VBA end.
Code it and see it – Web Services
Now, let’s start writing a sample Web Method, which will return a DataSet. This DataSet contains one DataTable and this DataTable contains 3 columns – EmpNo, EmpName and DeptNo. The rows in this DataTable are going to get populated in our Excel. So, let’s have another method in the same SampleService class and we call this as GetEmployeeDetails. Here is the entire code for the web method:
<WebMethod()> _
Public Function GetEmployeeDetails() as DataSet
Dim dsEmpDet as new DataSet
Dim objCon as new SqlConnection()
Dim objCmd as new SqlCommand
Dim objDA as SqlDataAdapter
objCmd.CommandText = ”Select EmpNo, EmpName, DeptNo from Emp”
objCmd.CommandType = CommandType.Text
objCmd.Connection = objCon
DA = new SqlDataAdapter(objCmd)
DA.Fill(dsEmpDet)
objConn.Close
Return dsEmpDet
End Function
Ensure that you have Try – Catch blocks when you actually write the code in your web services. Now build it and your web service class now contains one more method, GetEmployeeDetails(), which contains no parameter (for brevity).
Get it at the VBA end, but not as DataSet
Now, if you are using the same Excel workbook, which we used for our first sample HelloWorld web method, you need to remove the clsws_SampleService class and re-reference it, as our Web Service contains a new method. To remove, right-click on the class file and click on “Remove …”. It will ask you whether you need to import the class to your hard disk before deleting. We don’t need that and hence, click on NO and proceed.
Once you have referenced the web service again, go to the same Open event of the Workbook, where we have written the VBA code for the HelloWorld method. Comment the line where you call the HelloWorld web method and then write the following code for calling the GetEmployeeDetails method.
Public Sub Workbook_Open()
On error goto errhandler
Dim objXmlDoc as New DOMDocument
Dim objNList as IXMLDOMNodeList
Dim objWS as new clsws_SampleService
Dim objTableNodes as IXMLDOMNodeList
Dim objDataNode as IXMLDOMNode
Dim RowCount as Integer
Dim ColCount as Integer
Set objNList = objWS.wsm_GetEmployeeDetails
If Not objNList is Nothing Then
objXmlDoc.LoadXml objNList(1).XML
Set objTableNodes = objXmlDoc.getElementsByTagName("Table")
If Not objTableNodes Is Nothing Then
RowCount = objTableNodes.Length
If objTableNodes.Length > 0 Then
ColCount = objTableNodes(0).childNodes.Length
Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If
For i=0 to RowCount -1
Set objDataNode = objTableNodes(i)
For j=0 to ColCount -1
Sheet1.Cells(i + 2, j + 1) = objDataNode.childNodes(j).text
Next j
Next i
Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If
Sheet1.Activate
ThisWorkbook.Activate
End If
Exit Sub
Errhandler:
Msgbox err.Description
End Sub
As you would expect, I will explain it
Now, let us go through the above code once again. We have declared 3 Node List objects and one Node object. The objNList object is a Node List object that receives what has been returned from the Web Service. That is, the DataSet returned from the Web Service is given to us in the form of a Node List object.
Now, what does the Node List object contains? Well, the Node List object contains 2 Xml Node objects inside it. The first of those contains the Schema of the XML returned and the second contains the actual XML itself. The Schema looks like this:
And the Data part of the xml looks like this:
But we are interested in taking the second node of the Node List object – the actual Data part. That is why, we are writing this line of code:
objXmlDoc.LoadXml objNList(1).XML
Set objTableNodes = objXmlDoc.getElementsByTagName("Table")
We first load the data part to an DOM Document and taking the “Table” elements. Please refer from the above that the “Table” element actually contains the data. So, the objTableNodes will contain all the “Table” nodes, in our example since 3 rows are returned, objTableNodes will contain 3 Node objects.
Now, we know that each Table node is actually a row from your back end store. The columns for each row will also be nodes, but as child nodes under the “Table” node. Thus EmpNo, EmpName and DeptNo will be child nodes under each table node. Have a look at this FOR loop in the above code:
For i=0 to RowCount -1
Set objDataNode = objTableNodes(i)
For j=0 to ColCount -1
Sheet1.Cells(i + 2, j + 1) = objDataNode.childNodes(j).text
Next j
Next i
First, the objDataNode object will contain the current “Table” node. Then, we loop through each child node under the objDataNode or in other words we loop through each column under each row and assign the value in the appropriate cell. The text property of the Xml Node object will contain the value for that column, for that row. Also see in the above example that the rows are populated into the excel sheet from the 2nd row, assuming that we have fixed headings in the first row of the excel sheet. Excel columns are numbered from 1 and hence the code j + 1.
That’s it, you have done it!!
Voila! How simple it was for us to call the web service some where around the globe and putting into excel isn’t it? It is just with the help of the Web Service Toolkit of course, which has made our life a lot, lot easier. This is the output sample:
Don’t go away, poke into proxy
Just we have achieved something, but we can’t afford to close our Excel books and go away. Let’s look into what is there inside the proxy class, clsws_SampleService. First, lets look at the declaration part.
Private sc_SampleService As SoapClient30
Private Const c_WSDL_URL As String = http://localhost/sampleservice/sampleservice.asmx?wsdl
Private Const c_SERVICE As String = "SampleService"
Private Const c_PORT As String = "SampleServiceSoap"
Private Const c_SERVICE_NAMESPACE As String = "http://tempuri.org/SampleService/Service1"
(Extra line spaces are added for brevity)
Once you reference a Web Service using the Web Service Toolkit, it adds a few constants in the proxy class. But the first line is the one that we have to look into. It actually includes the SOAP library and declares an object of a type SoapClient30. This object will be used from now on to call the web service methods.
Also, look at the code under the Class_Initialize method. This is where the Web Service gets instantiated.
Private Sub Class_Initialize()
Dim str_WSML As String
str_WSML = ""
Set sc_SampleService = New SoapClient30
sc_SampleService.MSSoapInit2 c_WSDL_URL, str_WSML, c_SERVICE, c_PORT, c_SERVICE_NAMESPACE
sc_SampleService.ConnectorProperty("ProxyServer")= ""
sc_SampleService.ConnectorProperty("EnableAutoProxy") = True
End Sub
(Actual comments provided by the proxy are taken out here for brevity)
The MsSoapInit2 method of the SoapClient30 class actually initiates the call to the Web Service, using the constants declared above. The ProxyServer and EnableAutoProxy settings are taken from the Internet Explorer settings. The actual method calls (for example, wsm_GetEmployeeDetails) are self-explanatory.
Now that brings an end to our tour of Web Services and VBA. Hope you have enjoyed this tour at every moment. This tour would have given you a fair idea of how things work. Hope to meet you with some more articles later on, but for now its
Bye! Bye!!
|
Responses
|
| Author: Peter Cooke 06 Jul 2004 | Member Level: Bronze Points : 0 | Really excellent. Very useful and concise. Thank you.
| | Author: mulmad 06 Feb 2008 | Member Level: Bronze Points : 0 | I'm getting the following error running the code:
Compile error:
Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic
The error refer to the following lines:
ColCount = objTableNodes(0).childNodes.Length
Sheet1.Cells(i + 2, j + 1) = objDataNode.childNodes(j).Text
In both cases the following code is marked.
childNodes.
|
|