rayhee1985
06-15-2020, 11:34 PM
Hello everyone,
I try to run the VBA code as below but it is compile error "Run time error "424":Object require" on Set GetJSON = JSONConverter.ParseJson(.responseText)
Appreciate someone can guide me to find out what is the error mean for?
Sub Test()
Const sURL As String = "https://eservices.mas.gov.sg/api/action/datastore/search.json?resource_id=95932927-c8bc-4e7a-b484-68a66a24edfe&fields=end_of_day,jpy_sgd_100&limit=10&sort=end_of_day%20desc"
Dim a, ws As Worksheet, json As Object, colData As Collection, m As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set json = GetJSON(sURL)
Set colData = json("result")("records")
a = CollectionToArray(colData)
With ws
.Cells.ClearContents
.Range("A1").Resize(1, 2).Value = Array("End Of Day", "Amount")
.Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End With
Set json = Nothing: Set colData = Nothing
End Sub
Function GetJSON(ByVal sURL As String) As Object
Dim http As MSXML2.XMLHTTP60
Set http = New MSXML2.XMLHTTP60
With http
.Open "Get", sURL, False
.setRequestHeader "User-Agent", "Mozilla/5.0"
.send
Set GetJSON = JSONConverter.ParseJson(.responseText)
End With
End Function
Function CollectionToArray(c As Collection) As Variant()
Dim a(), i As Long, j As Long
ReDim a(1 To c.Count, 1 To 2)
For i = 1 To c.Count
a(i, 1) = c.Item(i)("end_of_day")
a(i, 2) = c.Item(i)("jpy_sgd_100")
Next i
CollectionToArray = a
End Function
I try to run the VBA code as below but it is compile error "Run time error "424":Object require" on Set GetJSON = JSONConverter.ParseJson(.responseText)
Appreciate someone can guide me to find out what is the error mean for?
Sub Test()
Const sURL As String = "https://eservices.mas.gov.sg/api/action/datastore/search.json?resource_id=95932927-c8bc-4e7a-b484-68a66a24edfe&fields=end_of_day,jpy_sgd_100&limit=10&sort=end_of_day%20desc"
Dim a, ws As Worksheet, json As Object, colData As Collection, m As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set json = GetJSON(sURL)
Set colData = json("result")("records")
a = CollectionToArray(colData)
With ws
.Cells.ClearContents
.Range("A1").Resize(1, 2).Value = Array("End Of Day", "Amount")
.Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End With
Set json = Nothing: Set colData = Nothing
End Sub
Function GetJSON(ByVal sURL As String) As Object
Dim http As MSXML2.XMLHTTP60
Set http = New MSXML2.XMLHTTP60
With http
.Open "Get", sURL, False
.setRequestHeader "User-Agent", "Mozilla/5.0"
.send
Set GetJSON = JSONConverter.ParseJson(.responseText)
End With
End Function
Function CollectionToArray(c As Collection) As Variant()
Dim a(), i As Long, j As Long
ReDim a(1 To c.Count, 1 To 2)
For i = 1 To c.Count
a(i, 1) = c.Item(i)("end_of_day")
a(i, 2) = c.Item(i)("jpy_sgd_100")
Next i
CollectionToArray = a
End Function