Perform HTTP Post from within Excel and Parse Results

Question

I have access to an API. The API takes an XML post as input and then returns an XML response with the relevant data.

I want to

1. Send the HTTP Post to the Server (Authentication and Request will be sent together)
2. Receive the response (One of the options to be returned is CSV or XML)
3. Insert the data into the appropriate rows and columns and then perform data analysis using pivot tables.

I don't have a programming background in excel but am comfortable with different web scripting languages, HTML, CSS, Javascript etc.

Any ideas?

Answer 1

If you need to send your input xml as the message body here is how you can do it. You may need to add more or change the Request headers to get it to work for you.

Using the DOMDocument object make it easy to work with your xml documents.

Add a project references to;

• Microsoft WinHTTP Services, version 5.1
• Microsoft XML, v6.0

Example:

Dim xmlInput As String
xmlInput = "<YourXmlRequest></YourXmlPayload>"

Dim oXmlHttp As MSXML2.XMLHTTP60
Set oXmlHttp = New MSXML2.XMLHTTP60

oXmlHttp.Open "POST", serviceURL, False, "UserName", "Password"
oXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
oXmlHttp.setRequestHeader "Connection", "Keep-Alive"
oXmlHttp.setRequestHeader "Accept-Language", "en"

oXmlHttp.send xmlInput

Debug.Print oXmlHttp.responseText

Dim oXmlReturn As MSXML2.DOMDocument60
Set oXmlReturn = New MSXML2.DOMDocument60
oXmlReturn.loadXML oXmlHttp.responseText

Answer 2

The Excel request side can be handled with this VBA code.

Sub GetStuff()

Dim objXML As Object
Dim strData As String
Dim strResponse As String

 strData = "Request"
 Set objXML = CreateObject("MSXML2.XMLHTTP")

 objXML.Open "POST", "www.example.com/api?" & strData, False
 objXML.Send
 strResponse = objXML.responsetext

MsgBox strResponse

End Sub

Answer 3

I suggest to use WinHttp.WinHttpRequest.5.1 instead of MSXML2.XMLHTTP whenever you need windows authentication, because it allows you to use current user credential to login. Here is an example

Dim http As Object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
http.SetAutoLogonPolicy 0
http.Open "POST", "http://myUrl.html?param1=value1", False
http.setRequestHeader "Content-Type", "text/json"
http.setRequestHeader "User-Agent", "Mozilla/5.0 (iPad; U; CPU OS 3_2_1 like Mac OS X; en-us) AppleWebKit/531.21.10 (KHTML, like Gecko) Mobile/7B405"
http.send ("")

Reference: https://github.com/VBA-tools/VBA-Web/issues/15

Answer 4

This is what I ended up using:

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objHTTP.Open "POST", urlPath, False
objHTTP.setRequestHeader "Content-Type", "text/xml"
objHTTP.send (request)