Open happyhumorist opened 7 months ago
After redownloading the .bas file and starting a new spreadsheet it now works.
It still won't work with the initial file. I'm not sure why.
What you have is not JSON. Need to use double quotes. { "id": "p01", "name": "name 1", "price": 4.5, "quantity": 20 }
When in doubt, I use an online JSON validator. https://jsonlint.com/ Did you know that ChatGPT 3.5 knows about this library and in fact recommends it?
@happyhumorist
I got exactly the same problem. I am trying to receive data through a http post request. here is my code:
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
Url = "Some URL" objHTTP.Open "POST", Url, False objHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)" objHTTP.SetRequestHeader "Content-type", "application/json" objHTTP.SetRequestHeader "Accept", "application/json" objHTTP.send
Set JSON = ParseJson(objHTTP.responseText)
i=1 For Each Item In JSON Sheets(1).Cells(i, 1) = Item("SomeItemName") i = i + 1 Next
The Code works in one workbook and it does not in the other. In both workbooks I imported the same JsonConverter.bas file. The error looks like this:
and when I go to the debugger it shows me that the code runs into a problem in the following line:
json_ParseObject.Item(json_Key) = json_ParseValue(json_String, json_Index)
Help would be appreciated! Greetings
I've found the source of the KeyNotFoundError
. It is caused by the presence of the Selenium Type Library
in your VBA project. Selenium contains a Dictionary
object and the prioritization of the references can be such that VBA will use the Selenium version of the Dictionary object instead of the one from Microsoft Scripting Runtime
.
Here's a screenshot from the Object Browser (F2):
To solve this issue, make sure that Selenium Type Library
reference is sitting below Microsoft Scripting Runtime
by reducing its priority:
@DecimalTurn
That solved the problem! Many thanks! Appreciated!
I was following RedStapler's guide here: https://www.youtube.com/watch?v=CFFLRmHsEAs
But I kept getting errors involving this line of the JsonConverter Module: json_ParseObject.Item(json_Key) = json_ParseValue(json_String, json_Index)
Its saying Run-Time error'0': KeyNotFoundError Dictionary key not found :id
This is the code i'm trying to run:
Sub testJson()
Dim jsontext As String Dim productObject As Object jsontext = "{'id':'p01','name':'name 1','price':4.5,'quantity':20}" Debug.Print jsontext Set productObject = JsonConverter.ParseJson(jsontext) Debug.Print productObject("id")
End Sub
I have the MS Scripting Runtime checked in my references.
Do I have something misspelled?