Perform HTTP Post from within Excel and Parse Results

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

Sub GetStuff()Dim objXML As ObjectDim strData As StringDim strResponse As String strData = "Request" Set objXML = CreateObject("MSXML2.XMLHTTP") objXML.Open "POST", "www.example.com/api?" & strData, False objXML.Send strResponse = objXML.responsetextMsgBox strResponseEnd Sub

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 StringxmlInput = "<YourXmlRequest></YourXmlPayload>"Dim oXmlHttp As MSXML2.XMLHTTP60Set oXmlHttp = New MSXML2.XMLHTTP60oXmlHttp.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 xmlInputDebug.Print oXmlHttp.responseTextDim oXmlReturn As MSXML2.DOMDocument60Set oXmlReturn = New MSXML2.DOMDocument60oXmlReturn.loadXML oXmlHttp.responseText

This is what I ended up using:

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