2

I've been doing quite a bit of web scraping over the past year and at some point, for reasons I don't remember anymore, I decided to use the Microsoft WinHTTP Services version 5.1 library as my default solution when sending HTTP requests.

I've never had any problems with it and I have achieved anything I ever attempted to do as far as web scraping is concerned.

That is, until i tried the following:

Sub nse()
Dim req As New WinHttpRequest
Dim url As String, requestPayload As String

url = "https://www.niftyindices.com/Backpage.aspx/getHistoricaldatatabletoString"
requestPayload = "{'name':'NIFTY 50','startDate':'01-Feb-2020','endDate':'01-Feb-2020'}"

With req
    .Open "POST", url, False
    .setRequestHeader "Content-Type", "application/json; charset=UTF-8"
    .send requestPayload
    Debug.Print .responseText
End With

End Sub

The .send method fails with a

Run-time error -2147012894 (80072ee2) Automation error

Changing to Dim req As New MSXML2.XMLHTTP60 solves the issue completely.

What am I missing here? Could it be website specific somehow? Is there something in the inner workings of these 2 libraries I should know?

Any input would be appreciated.

DisappointedByUnaccountableMod
  • 6,656
  • 4
  • 18
  • 22
Stavros Jon
  • 1,695
  • 2
  • 7
  • 17
  • Have You tried `WinHttp.WinHttpRequest`? I found out this problem some days ago, realized that sometimes there is run time error during "speed tests". But if I saved workbook before test with `WinHttp.WinHttpRequest` everything went fine, I mean no run time error. – Teamothy Mar 02 '20 at 11:57
  • @Teamothy I have tried this as well but it doesn't make any difference to me :/ – Stavros Jon Mar 02 '20 at 11:59
  • 1
    While using `WinHttpRequest` the operating system defaults for HTTP requests - proxy settings for example - are not used and must be set explicitly. See https://stackoverflow.com/questions/49491042/excel-vba-accessing-json-file-operation-timed-out/49491854#49491854 – Axel Richter Mar 02 '20 at 12:28
  • @AxelRichter thanks for the info, that's useful to know! However, I'm not using a proxy in the system's settings. Plus this has only happened with this specific website so far. – Stavros Jon Mar 02 '20 at 12:37
  • I think it's because the protocol is http**s** and not http. Try it with the 'certificate trick'. (There is also a link to a stack overflow thread in the code) http://exceldevelopmentplatform.blogspot.com/2017/02/using-https-with-excel-vba.html – Zwenn Mar 02 '20 at 12:58
  • @Zwenn Hey thanks! That's useful stuff. However, which certificate should I send? Also, I've done https before without a problem... :/ – Stavros Jon Mar 03 '20 at 11:49

0 Answers0