vbapostweb-scrapingxmlhttprequestwinhttprequest

Empty response of an HTTP post request in VBA

I am trying a make an HTTP post request in VBA but getting an empty response. Here is my code:

Sub User()
    On Error Resume Next
    Dim HTTPreq As WinHttpRequest
    Set HTTPreq = New WinHttpRequest
    URL = "https://www.transfermarkt.com/site/DropDownWettbewerbe"
    HTTPreq.Open "POST", URL, False
    HTTPreq.setRequestHeader "user-agent", "Mozilla/5.0 (Windows NT 6.3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 UBrowser/7.0.185.1002 Safari/537.36"
    HTTPreq.send "land_id=189"
    MsgBox (HTTPreq.responseText)
End Sub

I have done the same thing in Python and get the expected response:

<pre><option value="">Competition</option><option value="GB1">Premier League</option><option value="GB2">Championship</option><option value="GB3">League One</option><option value="GB4">League Two</option><option value="CNAT">National League</option><option value="GB21">Premier League 2</option><option value="GB18">U18 Premier League</option><option value="GBFL">EFL Trophy</option><option value="FAC">FA Cup</option><option value="CGB">EFL Cup</option><option value="GBCS">Community Shield</option><option value="FAYC">FA Youth Cup</option>.

Not sure where I am wrong doing this in VBA.

Solution

  • You need to specify content-type header, the below code works fine for me:

    Sub User()

    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "POST", "https://www.transfermarkt.com/site/DropDownWettbewerbe", False
        .SetRequestHeader "content-type", "application/x-www-form-urlencoded; charset=UTF-8"
        .SetRequestHeader "user-agent", "user-agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64)"
        .Send "land_id=3"
        MsgBox .ResponseText
    End With

End Sub

    You may find out the necessary headers from a browser developer tools network tab:

    developer tools network tab