lundi 28 juin 2021

Web Data fetch is not updating

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