Hello,
I have the following code which passes a single static API GET:
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!
I have the following code which passes a single static API GET:
VBA Code:
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!