Runtime error 91 Object variable or With block variable not set

[carl-miranda]

Runtime error 91
Object variable or With block variable not set

I am trying to use this tool using Access 2016 to parse below JSON, but encountering above error:

{
"activities": [
{
"id": "5057005",
"action": "uploaded",
"stage_name": "Sourced",
"created_at": "2017-12-08T08:22:06.621Z",
"member": {
"id": "5f481",
"name": "Melissa Test"
},
"body": null
}
]
}

Kindly advise if there is any fix to this issue or point me to the right-direction on how to workaround this. I stepped through the code and "ParseJson" function is not assigned any value after reading the last character.

Thanks a lot!

[pauldiamond]

Hello,
I have the same problem the Parsejson function returns me zero where is the problem ?
You can help us
thanks a lot!
[{"561593":{"wyId":"561593","shortName":"V. Samsonyan","firstName":"Volodya","middleName":"","lastName":"Samsonyan","height":0,"weight":0,"birthDate":"0000-00-00","birthArea":{"id":"51","alpha2code":"AM","alpha3code":"ARM","name":"Armenia"},"passportArea":{"id":"51","alpha2code":"AM","alpha3code":"ARM","name":"Armenia"},"role":{"name":"Midfielder","code2":"MD","code3":"MID"},"foot":null,"currentTeamId":14934,"currentNationalTeamId":null,"gender":"male","status":"active"}, "109299":{"wyId":"109299","shortName":"A. Derdzyan","firstName":"Armen","middleName":"","lastName":"Derdzyan","height":174,"weight":67,"birthDate":"1993-11-17","birthArea":{"id":"51","alpha2code":"AM","alpha3code":"ARM","name":"Armenia"},"passportArea":{"id":"51","alpha2code":"AM","alpha3code":"ARM","name":"Armenia"},"role":{"name":"Midfielder","code2":"MD","code3":"MID"},"foot":"","currentTeamId":14934,"currentNationalTeamId":null,"gender":"male","status":"active"}}]

[ricardoba13]

You guys are not instantiating the class. If you guys give me the piece of VBA code that is having the issue, I would be able to give you the right code!

[pauldiamond]

Hi
With an API call I receive the JSON string:

{"players":{"561593":{"wyId":"561593","shortName":"V. Samsonyan","firstName":"Volodya","middleName":"","lastName":"Samsonyan","height":0,"weight":0,"birthDate":"0000-00-00","birthArea":{"id":"51","alpha2code":"AM","alpha3code":"ARM","name":"Armenia"},"passportArea":{"id":"51","alpha2code":"AM","alpha3code":"ARM","name":"Armenia"},"role":{"name":"Midfielder","code2":"MD","code3":"MID"},"foot":null,"currentTeamId":14934,"currentNationalTeamId":null,"gender":"male","status":"active"}, "109299":{"wyId":"109299","shortName":"A. Derdzyan","firstName":"Armen","middleName":"","lastName":"Derdzyan","height":174,"weight":67,"birthDate":"1993-11-17","birthArea":{"id":"51","alpha2code":"AM","alpha3code":"ARM","name":"Armenia"},"passportArea":{"id":"51","alpha2code":"AM","alpha3code":"ARM","name":"Armenia"},"role":{"name":"Midfielder","code2":"MD","code3":"MID"},"foot":"","currentTeamId":14934,"currentNationalTeamId":null,"gender":"male","status":"active"}}}

After the call through the code that follows step in the ParseJeson however I am returned a string zero and I can not withdraw anything

Public Sub GetPerson8(wyid As Long)
Dim db As Database, qdef As QueryDef
Dim FileNum As Integer
Dim DataLine As String, jsonStr As String, strSQL As String
Dim p As Object, element As Variant
Dim p2 As Object, element2 As Variant

Set reader = CreateObject("MSXML2.ServerXMLHTTP.6.0")
'reader.Open "GET", "https://apirest.wyscout.com/v2/teams/" + CStr(wyid) + "/squad"
reader.Open "GET", "https://apirest.wyscout.com/v2/updatedobjects?updated_since=2018-04-18 18:00:00&type=players"
reader.setRequestHeader "Accept", "application/json"
reader.send

y = reader.responseText

Set db = CurrentDb
If InStr(y, "message") = 0 Then
jsonStr = ""
FileNum = 1

jsonStr = jsonStr & y
z1 = Len(jsonStr)
j = Right(jsonStr, z1 - 11)
z2 = Len(j)
j2 = Left(j, z2 - 1)
z = "[" + j2 + "]"
  
Set p = ParseJson(z)


' ITERATE THROUGH DATA ROWS, APPENDING TO TABLE

If Mid(z, 3, 4) <> "code" Then
For Each element In p

 strSQL = "PARAMETERS [wyId] Long,[gsmId] Long, [shortName] Text(255), [firstName] Text(255),[lastName] Text(255), " _
                 & "[birthDate] Text(255), [height] integer,[weight] integer, [ruolo] Text(255), [nazione] Text(255), [nazione1] Text(255),[foot] Text(255),[currentTeamId] Long ,[currentNationalTeamId] Long,[gender] Text(255),[status] Text(255);" _
                 & "INSERT INTO Player (wyid, ObjId, Alias, Name, lastName, birthDate, Altezza, Peso, Ruolo, Nazione, Nazione1, Piede, currentTeamId, currentNationalTeamId, gender, status)" _
                 & "VALUES([wyid],[gsmId], [shortname], [firstName],[lastName], [birthDate], [height], [weight], [ruolo], [nazione], [nazione1],[foot],[currentTeamId],[currentNationalTeamId],[gender],[status]);"
 
  Set qdef = db.CreateQueryDef("", strSQL)

  qdef!wyid = element("wyId")
  qdef!gsmid = element("gsmId")
  qdef!ShortName = element("shortName")
  qdef!FirstName = element("lastName") + " " + element("firstName")
  qdef!LastName = element("lastName")
  qdef!birthDate = format(element("birthDate"), "dd/mm/yy")
  qdef!Height = element("height")
  qdef!Weight = element("weight")
  birthArea = element("birthArea")("name")
  qdef!Nazione = birthArea
  passportArea = element("passportArea")("name")
  qdef!Nazione1 = passportArea
  role = element("role")("name")
  qdef!Ruolo = role
  qdef!Foot = element("foot")
  qdef!currentTeamId = element("currentTeamId")
  qdef!currentNationalTeamId = element("currentNationalTeamId")
  qdef!gender = element("gender")
  qdef!Status = element("status")
  
qdef.Execute

Next element

End If
Else
End If
Set element = Nothing
Set p = Nothing

End Sub

In all the other calls I make I am returned the parsed string. The only thing I noticed that the other strings I call do not have the part before the word wyid "561593 ": that is present every new entry

{
"wyId": 34506,
"gsmId": 16093,
"shortName": "H. Zeybekoglu",
"firstName": "Hüsnü",
"middleName": "",
"lastName": "Zeybekoglu",
"height": 0,
"weight": 0,
"birthDate": "1985-03-13",
"birthArea": {
"id": "792",
"alpha2code": "TR",
"alpha3code": "TUR",
"name": "Turkey"
},
"passportArea": {
"id": "792",
"alpha2code": "TR",
"alpha3code": "TUR",
"name": "Turkey"
},
"role": {
"name": "Midfielder",
"code2": "MD",
"code3": "MID"
},
"foot": "",
"currentTeamId": 59720,
"currentNationalTeamId": null,
"gender": "male",
"status": "active"
}

Thanks a lot!

[ricardoba13]

Is that happening on
Set p = ParseJson(z)

or

For Each element In p

?

That error happens when an Object Is Nothing and you are trying to use it (either with a property, method or as an instance).

If you don't mind. You cand replicate your problem into a standard module and you can paste the entire code here. I will grad that text, fix it and give it back to you!

[pauldiamond]

Hi,
this the complete code

Option Compare Database

Public Function GetAllPersons8()

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from t_persons")
Do Until rec.EOF
DoCmd.Hourglass True

    'per ogni ID trovato nella tabella, procedo con il recupero dati e relativo salvataggio del risultato nella stessa tabella
    GetPerson8 (rec("IDPerson"))
    num = num + 1
rec.MoveNext
DoCmd.Hourglass False
Loop
rec.Close
db.Close
Set rec = Nothing
Set db = Nothing

MsgBox ("Caricamento completato")
'MsgBox ("Tempo Elaborazione ") & format(Tempo - Time, "hh:mm")
MsgBox ("Numero squadre chiamate ") & num
num = 0

End Function

Public Sub GetPerson8(IDPerson As Long)
Dim db As Database, qdef As QueryDef
Dim FileNum As Integer
Dim DataLine As String, jsonStr As String, strSQL As String
Dim p As Object, element As Variant
Dim p2 As Object, element2 As Variant

Set reader = CreateObject("MSXML2.ServerXMLHTTP.6.0")
'reader.Open "GET", "https://apirest.wyscout.com/v2/teams/" + CStr(wyid) + "/squad"
reader.Open "GET", "https://apirest.wyscout.com/v2/updatedobjects?updated_since=2018-04-18 18:00:00&type=players"
    reader.setRequestHeader "Accept", "application/json"
reader.send

y = reader.responseText

Set db = CurrentDb
If InStr(y, "message") = 0 Then
jsonStr = ""
FileNum = 1

jsonStr = jsonStr & y
z1 = Len(jsonStr)
j = Right(jsonStr, z1 - 11)
z2 = Len(j)
j2 = Left(j, z2 - 1)
z = "[" + j2 + "]"
  
Set p = ParseJson(z)


' ITERATE THROUGH DATA ROWS, APPENDING TO TABLE

If Mid(z, 3, 4) <> "code" Then
For Each element In p

 strSQL = "PARAMETERS [wyId] Long,[gsmId] Long, [shortName] Text(255), [firstName] Text(255),[lastName] Text(255), " _
                 & "[birthDate] Text(255), [height] integer,[weight] integer, [ruolo] Text(255), [nazione] Text(255), [nazione1] Text(255),[foot] Text(255),[currentTeamId] Long ,[currentNationalTeamId] Long,[gender] Text(255),[status] Text(255);" _
                 & "INSERT INTO Player (wyid, ObjId, Alias, Name, lastName, birthDate, Altezza, Peso, Ruolo, Nazione, Nazione1, Piede, currentTeamId, currentNationalTeamId, gender, status)" _
                 & "VALUES([wyid],[gsmId], [shortname], [firstName],[lastName], [birthDate], [height], [weight], [ruolo], [nazione], [nazione1],[foot],[currentTeamId],[currentNationalTeamId],[gender],[status]);"
 
  Set qdef = db.CreateQueryDef("", strSQL)

  qdef!wyid = element("wyId")
  qdef!gsmid = element("gsmId")
  qdef!ShortName = element("shortName")
  qdef!FirstName = element("lastName") + " " + element("firstName")
  qdef!LastName = element("lastName")
  qdef!birthDate = format(element("birthDate"), "dd/mm/yy")
  qdef!Height = element("height")
  qdef!Weight = element("weight")
  birthArea = element("birthArea")("name")
  qdef!Nazione = birthArea
  passportArea = element("passportArea")("name")
  qdef!Nazione1 = passportArea
  role = element("role")("name")
  qdef!Ruolo = role
  qdef!Foot = element("foot")
  qdef!currentTeamId = element("currentTeamId")
  qdef!currentNationalTeamId = element("currentNationalTeamId")
  qdef!gender = element("gender")
  qdef!Status = element("status")
  
qdef.Execute

Next element

End If
Else
End If
Set element = Nothing
Set p = Nothing

End Sub

the parsejeson that i use
VBA-JSON v2.2.1

Tkanks a lot!

[ricardoba13]

GitHubHelp.zip

There you go @pauldiamond

I changed it a bit. Now you can adapt it the way you want.

Let me know how it goes!

[pauldiamond]

Hi Ricardo,
you go great I have adapted the code everything matters. But I have one more thing to ask you. There are some cases that I have to import the data directly from a file and not through an API call
The file can be in a specific folder so I should include the path
You can help me
Thanks a lot!

[ricardoba13]

You named that string as "Y". Use that Y to be either the api response or the text inside that file then.

That's all I can help you with. Sorry.

[pauldiamond]

Hi Ricardo,
I will follow your advice
Thank you very much

[carl-miranda]

Hi @ricardoba13 ,

For us the error happens on the set obj = JsonConverter.ParseJson call. Kindly see below code snippet:

'get response from API
respText = CallApi(nextUrl)

    Dim jsonObj As Object
    'parse the Json response using JsonConverter
    Set jsonObj = JsonConverter.ParseJson(respText)

I am using the latest VBA-JSON version (VBA-JSON v2.2.3).

Many Thanks,
Carl

[timhall]

@carl-miranda I tried your original json in Access and didn't have any issues parsing it. What is respText in your above error?

[carl-miranda]

@timhall the respText was the JSON string returned from the API we are calling. I downloaded the latest version VB-JSON 2.2.4 and it resolved the issue when I tested.

Thank you for looking into this, much appreciated.