One advantage of calling a web service directly, rather than through proxies, is that it is very easy to handle the response asynchronously. The DOMDocument object provides an ondataavailable event that occurs when the object is finished loading XML from a source. This means that you can launch a web service request, release control to the user, and display results when a request is complete. Being able to handle a response asynchronously is especially important when the web service is returning a large amount of data.
Declare a DOMDocument object at the module of a class. The class can be a workbook, worksheet, or code class module. For example, the following variable is declared in the wsAmazon worksheet class:
Dim WithEvents xdoc As DOMDocument
Private Sub xdoc_ondataavailable( ) End Sub
In other code, initialize the xdoc object, set its
asyncproperty to True, and then call the web service using the xdoc object’s
Loadmethod. For example, the following event procedure searches Amazon.com for a keyword when the user clicks the Get Titles button on the wsAmazon worksheet:
Sub cmdTitles_Click( ) Dim SearchUrl As String ' Create a new DOMDocument and set its options Set xdoc = New DOMDocument xdoc.async = True ' Create the search request SearchUrl = "http://xml.amazon.com/onca/xml2" & _ "?t=" & "webservices-20" & _ "&dev-t=" & "D1UCR04XBIF4A6" & _ "&page=1" & _ "&f=xml" & _ "&mode=books" & _ "&type=lite" & _ "&KeywordSearch=" & txtSearch.Text ' Issue the request and wait for it to be honored Loaded = xdoc.Load(SearchUrl) End Sub
Add code to the ondataavailable event procedure to respond to the web service data once it is returned. For example, the following code imports the result through an XML map and displays it in a list:
Private Sub xdoc_ondataavailable( ) Dim wb As Workbook ' Import the results through an XML Map into a list. Set wb = ThisWorkbook wb.XmlImportXml xdoc.XML, wb.XmlMaps("ProductInfo_Map"), True End Sub
When you run the preceding code by clicking on the Get Titles button, Excel returns control to the user as soon as the click is done. The list is updated once the web service responds.
The Microsoft SOAP type library does not support asynchronous calls, so you can’t use web services that only provide a SOAP interface asynchronously from Excel. The SOAP tools available with .NET do support asynchronous calls, however, so if you are programming with Visual Basic .NET outside of Excel, you can make asynchronous SOAP calls.