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")
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
I checked page response in network tab,it shows only the GET response,so thats why i used GET