Call Web Services From VBA

Created a class to call web services from VBA.

In the past the only thing available for calling web services was Microsoft’s Soap Toolkit. In order to use this component, you would have to redistribute the OCX. Unfortunately, the toolkit hasn’t been updated since 2003 and is no longer supported. IMHO it was cumbersome to use anyway.

The class I wrote uses MSXML’s XMLHTTP to do all the heavy lifting. For testing, I created a simple web service with six methods :

HelloWorld returns the string “Hello World”.
SayHello takes first name and last name parameters and returns a greeting.
GetAFile takes an integer image index and returns an image file byte array.
GetXml returns a demonstration xml string.
GetDataTable takes number of  data table rows to return.
SendAFile receives a file byte array and returns byte size string.

The class auto-detects the response data type – string, xml, data table or file data. Web services return byte arrays as base 64 encoded strings which I decode into a byte array.

Initially I used MSXML for iterating data table xml but found it to be too slow. Switching to XSLT and parsing the xml as a string was much faster. In the attached workbook I retrieve and write 30,000 cells worth of data table data in under 1 second. Luckily I created a page that assists transforming XML 😉

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.