Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using Excel VBA code to send a POST and retrieve data

Tags:

post

excel

vba

What VBA code would allow me to send a POST request (in a asp search box) and then to retrieve data between a <span id="xxx"></span> tag? (in the resulting page)

I have the following code that simulates a search request in the page:

 Dim Site As Object
 Set Site = CreateObject("InternetExplorer.application")
 Dim QNUMBER As String
 Dim URL As String

 URL = "apps/inventory/Default.aspx" 'local website
 QNUMBER = textBox_Scan.Text
 Site.navigate URL
 While Site.busy
 Wend
 Dim oHTMLDoc As Object
 Set oHTMLDoc = Site.document

 oHTMLDoc.getElementById("input_search").Value = QNUMBER
 oHTMLDoc.getElementById("btn_search").Click

It doesnt feel "clean" to do it that way and I feel that sending a POST request would be more appropriate.

Thanks.

[edit]

This is the form code

 <form name="aspnetForm" method="post" action="Default.aspx" id="aspnetForm">

The input text id

id="input_search" 

The submit button code

id="btn_search"

and I'd like to get data from <span id="warranty">36 month</span> and <span id="budget">500$</span>

like image 210
Darius M Avatar asked Jul 24 '17 15:07

Darius M


1 Answers

Sub macroPOST()
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = "[Your URL]"
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    objHTTP.Send ("id=dddaaa&pwd=1234[Your request parameters]")

    replyTXT = objHTTP.responseText

    If objHTTP.Status = "200" Then 'success
        MsgBox replyTXT
    Else
        'Do something
    End If
End Sub

It works on my Excel, thank you for reading carefully.

like image 157
Tiefan Ju Avatar answered Sep 30 '22 13:09

Tiefan Ju