Invoke WebService With Parameter Using Excel VBA (XMLHTTP Post)

I recently had a need to communicate with a Webservice that required a parameter to be invoked.

After reading some information online, I came to know that the Microsoft SOAP Toolkit was not available in Post 2007 Version. Mine was 2013 when I wrote this post.

So I started seeking help online, and a gentleman named Kyle helped me out with this code.

I noticed a lot of people were struggling with the same problem, and hence I decided to put up this blog post.

Before we look at the code, I want you to check out this WebService link.

Notice the Parameter name is “symbol”. Now try entering any stock symbol in the Value Text Box, lets try “AAPL” for now, and click on the Invoke button.

Notice the XML String that is being displayed with different XML Tags.

The below code will access this XML String and get values for 4 of these XML Tags, them being, Name, Open, High & Low and print those values to the immediate window.

It will also print the name of the Stock on Range A1 of the active sheet.

This code uses early binding, so it is important to set the required reference to the correct object library.

While in VBA, under the Tools menu select References, then select Microsoft XML, v6.0.

Now put the below code in a standard code module.

----------------------------------------------------

Sub PostStockDetails() Dim Request As XMLHTTP Dim Parameter1 As String Dim Doc As DOMDocument60 Parameter1 = "AAPL" Set Request = New XMLHTTP Set Doc = New DOMDocument60 With Request .Open "POST", "http://www.webservicex.net/stockquote.asmx/GetQuote", False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .send "symbol=" & Parameter1 'Name & Value of the Parameter Doc.LoadXML .responseText End With Doc.LoadXML Doc.Text MsgBox Doc.xml ActiveSheet.Range("A1").Value = Doc.SelectSingleNode("//Name").Text Debug.Print "Name - " & Doc.SelectSingleNode("//Name").Text, _ "Open - " & Doc.SelectSingleNode("//Open").Text, _ "High - " & Doc.SelectSingleNode("//High").Text, _ "Low - " & Doc.SelectSingleNode("//Low").Text End Sub

----------------------------------------------------

Learning how to communicate with WebServices opens up a lot of new doors for you. Feel free to experiment and try out new stuff.

Adios! :)

#VBA #Excel #WebService #XMLHTTP #SOAP #MSSOAPToolkit

Follow Us
  • Twitter Basic Square
  • Google+ Basic Square
Recent Posts
Search By Tags
No tags yet.