1
I have the code below that is wrong on this line:
Set JSON = JsonConverter.ParseJson(response)
Below is the complete code.
Option Explicit
Sub GetCompanyInformation()
Dim hReq As Object, JSON As Dictionary
Dim i As Long
Dim var As Variant
Dim ufCompaniesReturned As Variant
Dim ws As Worksheet
Dim Item As Variant
Set ws = Worksheets(Planilha1.CodeName)
'create our URL string and pass the user entered information to it
Dim strUrl As String
strUrl = "http://dev.markitondemand.com/MODApis/Api/v2/Lookup/json?input=1" '& ws.[Search_For_Company]
Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "GET", strUrl, False
.Send
End With
'wrap the response in a JSON root tag "data" to count returned objects
Dim response As String
response = "{""data"":" & hReq.ResponseText & "}"
Set JSON = JsonConverter.ParseJson(response)
'set array size to accept all returned objects
ReDim var(JSON("data").Count, 1)
i = 1
'loop through returned data and get Symbol & Name
For Each Item In JSON("data")
var(i, 0) = Item("Symbol")
var(i, 1) = Item("Name")
i = i + 1
Next
ufCompaniesReturned.lbCompaniesReturned.List = var
Erase var
Set var = Nothing
Set hReq = Nothing
Set JSON = Nothing
If ufCompaniesReturned.Visible = False Then
ufCompaniesReturned.Show
End If
End Sub
What is the content of Sponse?
– Felipe Avelar
You installed the VBA-JSON? And enabled in references? Because this is not native to VBA... It is a 3rd party tool
– danieltakeshi
Thanks for the answers! Excel 2016: what do I have to enable in References? And even enabling, it is necessary to install VBA-JSON?
– Paulo Semblano
Please close the post.
– Paulo Semblano