Solution 1 :

This code runs fine for me. It uses both, XMLHTTP and MSHTML, so there are no side effects from components:

Sub xxx()
    Dim xhr As New MSXML2.XMLHTTP60
    Dim html As New MSHTML.HTMLDocument

    With xhr
        .Open "GET", "https://www1.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=BEL&instrument=FUTSTK&type=-&strike=-&expiry=30APR2020#", False
        .send
        'Debug.Print StrConv(.responseBody, vbUnicode)
        html.body.innerHTML = StrConv(.responseBody, vbUnicode)

    End With
    Debug.Print html.body.innerHTML

    Set xhr = Nothing
End Sub

Probably the problem is not on XMLHTTP. Do you have any HTTP proxy servers there? If IE works but XMLHTTP not, this means IE uses system settings for http proxy, but XMLHTTP does not. So you should initialise it with your proxy. It has the setProxy method.
By the way, if the object is declared as new you don’t have to initialize it once again with CreateObject:

    Dim ie As New InternetExplorer
    Set ie = CreateObject("InternetExplorer.Application")

Problem :

I am trying to pull the one parameter from NSE WEB SITE,
The url is https://www1.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=INDUSINDBK&instrument=FUTSTK&type=-&strike=-&expiry=30APR2020

I am able to scrape what ever i want using IE.Navigate(Internet explorer) method (Which opens the browser and get the data),but this takes long time,i want the results to be extracted fast,so i decided to go with “MSXML2.XMLHTTP60” method,when i try,the response text,it returns internal server error

Here below i have given my both the codes,please help me for scraping the data in MSXML2.XMLHTTP60 method

  • Working Code but takes time

    Sub NSE_Data_Pull()
    
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
    
        Dim ie As New InternetExplorer
        Set ie = CreateObject("InternetExplorer.Application")
    
        ie.Visible = True
        ie.navigate "https://www1.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=INFY&instrument=FUTSTK&type=-&strike=-&expiry=30APR2020#"
        Do
    
        DoEvents
        Loop Until ie.readyState = READYSTATE_COMPLETE
    
        Dim doc As HTMLDocument
        Set doc = ie.document
    
        doc.Focus
        ActiveSheet.Cells(2, 3) = doc.getElementById("pchangeinOpenInterest").innerText
    
        ie.Quit
        ie.Visible = True
        Set doc = Nothing
        Set ie = Nothing
    
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    
    End Sub
    
  • Need help here only: response text returns internal server error – Fast Method

    Sub NSE_Data_Pull()
    
        Dim xhr As MSXML2.XMLHTTP60, html As MSHTML.HTMLDocument
        Set xhr = New MSXML2.XMLHTTP60
        Set html = New MSHTML.HTMLDocument
    
        With xhr
            .Open "GET", "https://www1.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=BEL&instrument=FUTSTK&type=-&strike=-&expiry=30APR2020#", False
            .send
             html.body.innerHTML = StrConv(.responseBody, vbUnicode)
        End With
    
        Debug.Print html.body.innerHTML
       ActiveSheet.Cells(2, 3).Value = html.getElementById("pchangeinOpenInterest").innerHTML
    End Sub
    

Comments

Comment posted by NickSlash

Are you sure you want to use a GET request? Might need to be a POST

Comment posted by saravana

I checked page response in network tab,it shows only the GET response,so thats why i used GET

By