I'm a beginner. I tried but failed to pull web page response while using xmlhttprequest post method. I'm trying to fill web form using excel file by making a query string but its not working. I don't know whether querystring is correct and don't know how to enter captcha? Value of captcha can be seen by inspecting element. I'll be grateful if somebody can help. Ample of thanks in advance.
Excel file blueprint:
Cells(2,3) Cells(2,4) Cells(2,5) Cells(2,6) Cells(2,7) Cells(2,8)
p_qry_no p_firstname p_gender p_nationality p_dob Captcha
20120087048131 Annaliza 2 237 17-04-1978 <img src="/portal/pls/portal/inimm_db.DBPK_CAPTCHA.GenerateCaptcha?randomValue=7732517">
My Code is as follows:
Option Explicit
Public Sub GetData()
Dim res As Variant, QueryString As String, i As Long
Dim p_qry_no As Long, p_firstname As String, p_gender As Long, p_nationality As Long, p_dob As String
Dim sht As Worksheet, lastRow As Long
Set sht = ThisWorkbook.sheets("Visa")
With sht
lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
End With
For i = 2 To lastRow
'p_qry_no: 20120087048131
'p_qry_type: 2
'p_firstname: Annaliza
'p_gender: 2
'p_nationality: 237
'p_dob: 17-04-1978
QueryString = "{""p_qry_no"":""" & sht.Cells(i, 3) & """,""p_qry_type"":""2"",""p_firstname"":""" & sht.Cells(i, 4) & """,""p_gender"":""" & sht.Cells(i, 5) & """,""p_nationality"":""" & sht.Cells(i, 6) & """,""p_dob"":""" & sht.Cells(i, 7) & """}"
With New XMLHTTP60
.Open "POST", "http://ednrd.ae/portal/pls/portal/INIMM_DB.DBPK_VISAVALIDITY.PROCESS_QUERY_STATUS_REDIR", False
.setRequestHeader "User-Agent", "Mozilla/5.0"
.setRequestHeader "Content-Type", "application/json"
.send QueryString
res = .responseText
Debug.Print res
End With
sht.Cells(i, 8) = res
Next i
End Sub