Hi everyone,
I'm trying to import and parse the JSON data from the following link into excel using VBA:
https://www.alphavantage.co/query?f...ype=close&apikey=ES1RXJ7VF1C1L9N5&symbol=MSFT
Unfortunately, i'm not able to complete it as it keeps giving an error: Object doesn't support this property or method. Can someone please help me resolve?
All i need is to get the date that is listed a long with the SMA that is provided for it. The URL for the JSON file is actually in Sheet2 and is referenced in the code. The reason for this is because i will have multiple URLs that the code will need to loop through and import.
Here is a screenshot of expected out put.
https://imgur.com/a/p2TKD
Here is the code that i'm using:
I'm trying to import and parse the JSON data from the following link into excel using VBA:
https://www.alphavantage.co/query?f...ype=close&apikey=ES1RXJ7VF1C1L9N5&symbol=MSFT
Unfortunately, i'm not able to complete it as it keeps giving an error: Object doesn't support this property or method. Can someone please help me resolve?
All i need is to get the date that is listed a long with the SMA that is provided for it. The URL for the JSON file is actually in Sheet2 and is referenced in the code. The reason for this is because i will have multiple URLs that the code will need to loop through and import.
Here is a screenshot of expected out put.
https://imgur.com/a/p2TKD
Here is the code that i'm using:
Code:
Sub test()
Dim objHTTP As Object
Dim MyScript As Object
Dim x As Integer, NoA As Integer, NoC As Integer
Dim myData As Object
Set MyScript = CreateObject("MSScriptControl.ScriptControl")
MyScript.Language = "JScript"
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
For x = 1 To Application.CountA(Sheet2.Columns(1))
Sheets("Sheet1").Activate
Sheets(1).Cells.Clear
Sheets(1).Range("A1:D1").Font.Bold = True
Sheets(1).Range("A1:D1").Font.Color = vbRed
Sheets(1).Range("A1") = "DATE"
Sheets(1).Range("B1") = "SMA"
URL = Sheets(2).Cells(x, 1)
objHTTP.Open "GET", URL, False
objHTTP.Send
If objHTTP.ReadyState = 4 Then
If objHTTP.Status = 200 Then
Set RetVal = MyScript.Eval("(" & objHTTP.responseText & ")")
objHTTP.abort
Set MyList1 = RetVal.result.buy
NoA = Sheet1.Cells(65536, 1).End(xlUp).Row + 1
For Each myData In MyList1
Sheets(1).Cells(NoA, 1).Value = myData.Last_Refreshed
Sheets(1).Cells(NoA, 2).Value = myData.SMA
NoA = NoA + 1
Next
End If
End If
Next
Set MyList2 = Nothing
Set MyList = Nothing
Set objHTTP = Nothing
Set MyScript = Nothing
End Sub
Last edited: