I am beginner at VBA. I have done the below code by referring to lot of articles found online.
I am trying to fetch API data from a website. It is taking the first fetch and I need the data to be fetched every 5 mins. But it is not refreshing at all. What can I do? Can anyone have a look at the code and advise?
I am using the below code to get the JSON data and later I am extracting using a JSON parser.
Sub FetchOptionChain()
Dim Json As Object
Dim webURL, webURL2 As String, mainString, subString
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim dtArr() As String
Dim request, request2 As Object
Dim HTML_Content As Object
Dim requestString As String
webURL2 = "https://www.nseindia.com/"
webURL = "https://www.nseindia.com/api/option-chain-indices?symbol=BANKNIFTY"
subString = "Resource not found"
Set HTML_Content = CreateObject("htmlfile")
'Get the WebPage Content to HTMLFile Object
With CreateObject("msxml2.xmlhttp")
.Open "GET", webURL2, False
.send
End With
FetchAgain:
With CreateObject("msxml2.xmlhttp")
.Open "GET", webURL, False
'Found online that I have to add the below to remove the cached results. Adding this is hanging the excel and it never comes out of it. Excel is hanging here
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36"
.send
mainString = .ResponseText
If InStr(mainString, subString) <> 0 Then
' Data has not been fetched properly. Will wait two seconds and try again.
Application.Wait (Now + TimeValue("0:00:2"))
GoTo FetchAgain
Aucun commentaire:
Enregistrer un commentaire