Perform HTTP Post from within Excel and Parse Results

excel http winhttprequest post vba
Scott picture Scott · Nov 30, 2009 · Viewed 47.3k times · Source

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

Robert Mearns picture Robert Mearns · Nov 30, 2009

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

Related questions

How can I send an HTTP POST request to a server from Excel using VBA?

What VBA code is required to perform an HTTP POST from an Excel spreadsheet?

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More