How to consumer webservices through VBA (Excel or Word) - Part II

In my earlier article, I have discussed about consuming a simple Hello World web method from a sample service. Now, in this article, let's discuss about consuming complex types – those which are not just string or integer.

If you haven't seen part I of this article, you may read it here.

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)

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
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
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If

End If

Exit Sub
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 = ""
(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!!


Author: Peter Cooke06 Jul 2004 Member Level: Bronze   Points : 0

Really excellent. Very useful and concise. Thank you.

Author: mulmad06 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.

Author: Nikhil jain26 Sep 2011 Member Level: Bronze   Points : 0

I am also gettng the same error as above

Please help!!

  • 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: