VBA to send API Get with cell reference

[yits05]

Hello,

I have the following code which passes a single static API GET:

Sub Test()

    Dim objHTTP As Object

    Dim MyScript As Object



    Set objHTTP = CreateObject("MSXML2.XMLHTTP")

    URL = "https://sample-api.abcdef.com/data/query=123456"

    

    objHTTP.Open "GET", URL, False

    objHTTP.send

    Set MyScript = CreateObject("MSScriptControl.ScriptControl")

    MyScript.Language = "JScript"

    

    Set RetVal = MyScript.Eval("(" + objHTTP.responsetext + ")")

    MsgBox RetVal.USD

End Sub

However, I would like the VBA to loop through a list of parameters in sheet1, cells a2:a22 and replace the end of the URL ( query=123456) is with the content of each of these cells. Then, I would it to paste the results from each query in the same range in sheet2 (i.e. a2:a22).

Thank you!

 

[diddi]

Sub Test()
    Dim objHTTP As Object
    Dim MyScript As Object

    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    For Row = 2 To 22
        URL = "https://sample-api.abcdef.com/data/query=" & Trim(Sheets("Sheet1").Cells(Row, 1))

        objHTTP.Open "GET", URL, False
        objHTTP.send
    
        Set MyScript = CreateObject("MSScriptControl.ScriptControl")
    
        MyScript.Language = "JScript"
    
        Set RetVal = MyScript.Eval("(" + objHTTP.responsetext + ")")
        Sheets("Sheet2").Cells(Row, 1) = RetVal.USD
    Next Row
End Sub