jsonexcelvbahttp-request-parametersvba6

VBA : Request Payload as nested dictionary


I used to scrape data from www.niftyindices.com with following code in VBA.

However, they have changed the Requestpayload to nested dictionary:

{"cinfo":"{'name':'NIFTY 50','startDate':'01-May-2024','endDate':'30-May-2024','indexName':'NIFTY 50'}"}

Due to this I am getting Blank JSON Data. I am not able to redefine the Requestpayload

Thanks for any help in advance

url = "https://www.niftyindices.com/Backpage.aspx/getHistoricaldatatabletoString"
defaultPayload = "{'name':'NIFTY 50','startDate':'','endDate':''}"
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A2") 

Set payloadJSON = JsonConverter.ParseJson(defaultPayload)
payloadJSON("startDate") = Day(startD) & "-" & MonthName(Month(startD), True) & "-" & Year(startD) '01-Feb-2020
payloadJSON("endDate") = Day(endD) & "-" & MonthName(Month(endD), True) & "-" & Year(endD) '29-Feb-2020
requestPayload = JsonConverter.ConvertToJson(payloadJSON)

With req
    .Open "POST", url, False
    .setRequestHeader "Content-Type", "application/json; charset=UTF-8"
    .setRequestHeader "X-Requested-With", "XMLHttpRequest"
    .send requestPayload
    Set responseJSON = JsonConverter.ParseJson(.responseText)
End With

Debug.Print responseJSON("d")

Solution

  • Something like this:

    Sub Tester()
    
        Dim dict As Object, dict2, startD, endD, requestPayload
        
        startD = Date     'for example
        endD = Date + 30  'for example
        
        Set dict = CreateObject("scripting.dictionary")
        dict("name") = "NIFTY 50"
        dict("indexName") = "NIFTY 50"
        dict("startDate") = Format(startD, "dd-mmm-yyyy") '01-Feb-2020
        dict("endDate") = Format(endD, "dd-mmm-yyyy") '29-Feb-2020
        
        Set dict2 = CreateObject("scripting.dictionary")
        dict2("cinfo") = JsonConverter.ConvertToJson(dict)
        
        requestPayload = JsonConverter.ConvertToJson(dict2)
        
        Debug.Print requestPayload
        
    End Sub
    

    Not really a nested dictionary though - it's a dictionary with one entry where the value is a JSON-formatted string.