Hi,

I have the code below which was supposed to be used for this structure of JSON file:

https://min-api.cryptocompare.com/da...t=10&e=Bittrex

However, now I need to adjust the Macro to be able to extract data in this structure of JSON file:

https://bittrex.com/api/v1.1/public/...-LTC&type=both

I want the final format to look like the attached image.

https://imgur.com/a/2dZTo

What would the edited code look like for this?

Here is the code I'm currently using:


Sub PRICEINCR()

    
    Dim objHTTP As Object
    Dim MyScript As Object
    Dim myData As Variant
    Dim myLength As Integer
    Dim NoA As Long

    'Clean the sheet
    Sheet1.Cells.Clear
    Sheet1.Activate
    
    'Write labels of the key in the table to the sheet
    Sheet1.Range("B1") = "time"
    Sheet1.Range("C1") = "close"
    Sheet1.Range("D1") = "high"
    Sheet1.Range("E1") = "low"
    Sheet1.Range("F1") = "open"
    Sheet1.Range("G1") = "volumefrom"
    Sheet1.Range("H1") = "volumeto"
    Sheet1.Range("B1:H1, J1:J2").Font.Bold = True
    Sheet1.Range("B1:H1, J1:J2").Font.Color = vbRed
    
    'The returned JSon table contents have the primary key/label named as "Data"
    'We are going to refer this "Data" in the following two JScripts "getValue" and "getLength"
    Set MyScript = CreateObject("MSScriptControl.ScriptControl")
    MyScript.Language = "JScript"
    MyScript.AddCode "function getValue(JSonList, JItem, JSonProperty) { return JSonList.Data[JItem][JSonProperty]; }"
    MyScript.AddCode "function getLength(JSonList) { return JSonList.Data.length; }"
    
    For x = 1 To Application.CountA(Sheet2.Columns(1))
    On Error Resume Next
        URL = Sheet2.Cells(x, 1)
        
        Set objHTTP = CreateObject("MSXML2.XMLHTTP")
        objHTTP.Open "GET", URL, False
        objHTTP.Send
        
        'Get the JSon table
        Set RetVal = MyScript.Eval("(" & objHTTP.responseText & ")")
        objHTTP.abort
        
        'Retrieve the value of the key "close" in the 4th item of the data set "Data"
        'with the help of the JScript function "getValue" above
        myData = MyScript.Run("getValue", RetVal, 4, "close")
        myLength = MyScript.Run("getLength", RetVal)
        
        'Get all the values of the JSon table under "Data"
        For i = 0 To myLength - 1
            NoA = Sheet1.Cells(65536, 1).End(xlUp).Row + 1
            Sheet1.Range("A" & NoA) = "Data -" & i
            Sheet1.Range("B" & NoA) = MyScript.Run("getValue", RetVal, i, "time") / (CDbl(60) * CDbl(60) * CDbl(24)) + #1/1/1970#
            Sheet1.Range("C" & NoA) = MyScript.Run("getValue", RetVal, i, "close")
            Sheet1.Range("D" & NoA) = MyScript.Run("getValue", RetVal, i, "high")
            Sheet1.Range("E" & NoA) = MyScript.Run("getValue", RetVal, i, "low")
            Sheet1.Range("F" & NoA) = MyScript.Run("getValue", RetVal, i, "open")
            Sheet1.Range("G" & NoA) = MyScript.Run("getValue", RetVal, i, "volumefrom")
            Sheet1.Range("H" & NoA) = MyScript.Run("getValue", RetVal, i, "volumeto")
        Next
        'Get the time info given in the JSon table
        Sheet1.Range("J" & NoA) = "TimeFrom:"
        Sheet1.Range("J" & NoA + 1) = "TimeTo:"
        Sheet1.Range("K" & NoA) = RetVal.TimeFrom / (CDbl(60) * CDbl(60) * CDbl(24)) + #1/1/1970#
        Sheet1.Range("K" & NoA + 1) = RetVal.TimeTo / (CDbl(60) * CDbl(60) * CDbl(24)) + #1/1/1970#
    Next
    
    Set objHTTP = Nothing
    Set MyScript = Nothing
End Sub