Calling a SOAP Web Service from Excel

If you’re doing some automation in Excel, you should know that you’re not limited to the world of Workbook and Worksheets. There’s a world out there that you can access from within Excel, and one of those worlds is the web API.

In this blog, I’ll show you the most basic (bare minimum) code to do just that.

Setup

Create a new Excel workbook as usual.
If you’re going to save this early, make sure you save that as “xlsm” to denote that it contains (VBA) macros.

Press “Alt + F11” to open the VBA IDE.

Add: Basic Module

Next, add a module to the workbook. See reference image below where to find it:

References

Then, go to “Tools” menu then “References”:

Add: WinHttp

Scroll down the list and locate “Microsoft WinHTTP Services” (see image below for reference).

We need this to perform the HTTP call, or to invoke the web API and get back the response.

Make sure to tick the box to select it.

Add: XML

Scroll further down again and select “Microsoft XML” (select the v3.0 as it’s easier to work using that).

We need this library to parse the SOAP response.

Code (VBA)

Now comes the fun part, writing codes 🙂

Enter the module we added so we can add codes to it. Once the “Module1” is open, type the codes below:

Function num_to_words(ByVal num As Integer) As String
    Dim url                     As String
    Dim xhttp                   As New WinHttp.WinHttpRequest
    Dim response                As String
    Dim xdoc                    As New MSXML2.DOMDocument
    
    url = "https://www.dataaccess.com/webservicesserver/NumberConversion.wso/NumberToWords?ubiNum="
    url = url & CStr(num)
    
    With xhttp
        .SetTimeouts 3000, 3000, 3000, 3000
        
        Call .Open("get", url, False)
        .Send
        .WaitForResponse
        
        response = .ResponseText
    End With
    
    If xdoc.LoadXML(response) Then
        response = xdoc.ChildNodes(1).nodeTypedValue
    End If
    
    num_to_words = response
End Function

Using GET method to call, instead of SOAP

A simpler way to consume a SOAP web service is to use either the GET or POST method equivalents. Most SOAP APIs do provide them, but if not, consult the developer or provider.

In this example, we used the GET method since it’s the simplest method to use.

All you got to do is get the parameters required by the API and use them as the GET URL parameter(s).

For this API, it only requires one parameter called “ubiNum” (take note that parameters are case-sensitive).

That’s why if you look back at the codes, the “ubiNum” is passed to the main URL along with its value.

Example:

?ubiNum=1234

Proof of Test

Open (or go back to) the worksheet to test it.

Here I entered a number in cell A1 and then, on cell B1, I entered this:

=num_to_words(A1)

After you entered that, our function will execute — call the web service along with the number and return the response.
The function then gets the actual conversion from the SOAP (XML) response and returns it.

Here’s what it looks like from my end of testing:

Reference(s) and/or Credit(s)

The num-to-words Web API

I just searched for “free SOAP api” and decided to use this simple number to words API in this URL:

https://www.dataaccess.com/webservicesserver/NumberConversion.wso?op=NumberToWords

I do not own that API or that domain. I just wanted to quickly get a public web api for this testing.

Feel free to test it if you want, and do search for more FREE APIs if you want to try something else.