Web Scraping with error

Asked

Viewed 45 times

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?

  • 1

    You installed the VBA-JSON? And enabled in references? Because this is not native to VBA... It is a 3rd party tool

  • Thanks for the answers! Excel 2016: what do I have to enable in References? And even enabling, it is necessary to install VBA-JSON?

  • Please close the post.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.