Perform HTTP Post from within Excel and Parse Results

excel, http, post, vba, winhttprequest

Solution

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

Problem

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 - Send the HTTP Post to the Server (Authentication and Request will be sent together) - Receive the response (One of the options to be returned is CSV or XML) - 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?

Original source