XMLHTTP "Post" not working (VBA)

Question

I am able use "POST" request in one website but there's a website wherein the POST method doesn't seem to work. I'm losing my mind already. :-(

Here's my test code for the site:

    Sub test()
    Dim result As String
    Dim myURL As String, postData As String
   Dim winHttpReq As Object
   Set winHttpReq = CreateObject("MSXML2.XMLHTTP")
   Dim ele
   Dim html As Object

   myURL = "http://www.mca.gov.in/DCAPortalWeb/dca/CompanyMaster.do?method=getName"
   postData = "taskID=9412&method=find&cmpnyname=&cmpnyID=U24232TN2004PLC054527"

   winHttpReq.Open "POST", myURL, False
   winHttpReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
   winHttpReq.send (postData)

   result = winHttpReq.responseText
   Sheets("sheet1").Range("A1") = result

   end sub

The home page of the site is "http://www.mca.gov.in/DCAPortalWeb/dca/MyMCALogin.do?method=setDefaultProperty&mode=31" and i used livehttp headers to get the POST URL link.

But it doesn't seem to work. I'm using company ID to search. sample ID: U24232TN2004PLC054527

Badly need your help. I think I am using incorrect URL here. But i used all the URLS showing in the live HTTP header and nothing seems to work. Please HELP!

Answer 1

The issue can be analyzed using Fiddler which provides much more details than livehttp headers. With Fiddler composer function, you can experiment with the POST requests and see the responses.

I checked with the URLs provided and saw that as the JSESSIONID cookie is not set in the request, the response comes back with a message "Your Session has expired".

The POSTed request:

POST http://www.mca.gov.in/DCAPortalWeb/dca/CompanyMaster.do HTTP/1.1
Content-Type: application/x-www-form-urlencoded
Host: www.mca.gov.in
Content-Length: 64

taskID=9412&method=find&cmpnyname=&cmpnyID=U24232TN2004PLC054527

The Response:

HTTP/1.1 200 OK
Date: Thu, 08 Jan 2015 11:31:55 GMT
Server: IBM_HTTP_Server
Surrogate-Control: no-store
Expires: Thu, 01 Dec 1994 16:00:00 GMT
Cache-Control: no-cache="set-cookie, set-cookie2"
X-UA-Compatible: IE=edge
Vary: Accept-Encoding
Content-Type: text/html;charset=ISO-8859-1
Content-Language: en-US
via: HTTP/1.1 proxy226
Proxy-Connection: Keep-Alive
Connection: Keep-Alive
Set-Cookie: JSESSIONID=0000NHNQgGMo247hf4dpRRcsrLP:17ufavb50; Path=/
Content-Length: 702

In the response body, we have the message "Your Session has expired".

If you first go to the home page, a cookie is set, which gets passed on to further calls. As you are directly raising the POST request, the JSESSIONID cookie is not set.

You need to open the home page first in your VBA code and then read the response header to identify the cookie, and use it to fill the POST request header. Also note that the POST has to be done to the URL "http://www.mca.gov.in/DCAPortalWeb/dca/CompanyMaster.do" without ?method=getName. Also the responsebody need to be used to create the data for filling in the excel file.

Sample Code:

Sub test_new()
    Dim result As String
    Dim myURL As String, postData As String
    Dim Cookie As String
    Dim winHttpReq As Object
    Dim oStream As Object
    Dim objStream As Object
    Set winHttpReq = CreateObject("MSXML2.XMLHTTP")

' GET the JSESSIONID cookie first frm home page
    myURL = "http://www.mca.gov.in/DCAPortalWeb/dca/MyMCALogin.do?method=setDefaultProperty&mode=31"
    winHttpReq.Open "GET", myURL, False
    winHttpReq.send

' Get the JSESSIONID from cookies ''' JSESSIONID=0000QSz0qJtUmQ8QRmEGBbVBFsm:18ungce99; Path=/
    Cookie = Split(winHttpReq.getResponseHeader("Set-Cookie"), ";")(0)
    myURL = "http://www.mca.gov.in/DCAPortalWeb/dca/CompanyMaster.do"       '   ?method=getName"
    postData = "taskID=9412&method=find&cmpnyname=&cmpnyID=U24232TN2004PLC054527"
    winHttpReq.Open "POST", myURL, False
    winHttpReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
' POST the request with cookies
    winHttpReq.setRequestHeader "Cookie", Cookie
    winHttpReq.send (postData)

' Get Text from response Body
    If winHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write winHttpReq.responseBody
        oStream.Position = 0
        oStream.Type = 2 'Text
        oStream.Charset = "UTF-8"
        result = oStream.ReadText
    End If
    Sheets("Sheet1").Range("A1") = result
End Sub

Note that this only saved the html code into the cell A1. If you really want to use the html content, you should use htmlfile object or save the stream to a local file with oStream.SaveToFile and use it instead of the code from oStream.Position = 0.