How can I use an Excel formula (no VBA) to retrieve a piece of data within an HTTP POST response?

Question

Is there any way to craft an Excel hyperlink formula that would send an HTTP POST on click and parse the response to populate another cell?

Answer 1

Public Function HttpPOST(ByVal URL As String) As String
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
   objHTTP.send ("")
   myText = objHTTP.responseText
   getHTTP2 = myText

End Function

Answer 2

[EDIT] Sorry, i didn't see "no vba" in your title. But i can't see how you would do.

You can do it with vba and find valuable information on these threads:

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

• Perform HTTP Post from within Excel and Parse Results

Here is a code sample:

'Send the request
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
   URL = "http://www.somedomain.com"
   objHTTP.Open "POST", URL, False
   objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
   objHTTP.send ("")

'Get the result
myText = objHTTP.responseText

'Set it in the current cell
ActiveCell.Value = myText

Regards,

Answer 3

Use built-in excel formula,

=WEBSERVICE(A2)

This will populate content of the URL in the cell.