Issue with JsonConverter

Question

this is the code I use to call parseJson in vba and in one case where I have a JSON object, I am receiving the error 10001 which relates to the latest Json-vba library 2.2.3 when the "{" or the "[" are expected.

Sub jsontest()
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")
'http.Open "GET", "https://bin.codingislove.com/document/ayequrimiy", False
http.Open "GET", "https://bin.codingislove.com/ayequrimiy.json", False


http.send
MsgBox (ParseJson(http.responseText)("Count"))
End Sub

The second .json file shows the 10001 error but the first one, the same file in text form, is perfectly executing. I tried as well including brackets when I call the json string without success.

What should I correct in my parser call?

If you open that second link in your browser and see the source code, you will see that there is no json there. The json is gotten via javascript. `MSXML.XMLHTTP` does not execute your javascript, so no json. — Palle Due, Jan 30 '18 at 12:53
@PalleDue so how it can do the same thing with the first link. it's the same file but text. — Leb_Broth, Jan 30 '18 at 13:07

score 1 · Answer 1 · answered Jan 30 '18 at 16:13

1

Since this isn't a JSON response, you will have to make it one before you can a parse it as such. The easiest approach is to load the DOM of the page, and then extract the text.

There are lots of snippets on SO (here's one) that'll do just that. Once you have the DOM, do something like this:

json = doc.getElementById("box").innerText

answered Jan 30 '18 at 16:13

Sam

5,424
1
18
33

in my case loading the don would be useless because I’m dealing with dynamic pages and it will return only the static objects. Am I mistaken maybe? – Leb_Broth Feb 01 '18 at 11:23
1

I think you are mistaken. Loading the DOM is the smoothest way of extracting text from a web page, dynamic or not. – Sam Feb 01 '18 at 12:06
I tried to see the response text from this page: https://bin.codingislove.com/ayequrimiy.json but only received the Head source with msxml2. How can I declare a don object in this case? – Leb_Broth Feb 01 '18 at 12:10
First - open the page in a web browser and the open the Console. Type this statement there: document.getElementById("box").innerText If that gives the expected result, then you have what you need. – Sam Feb 01 '18 at 12:26
it worked in the console however i wrote the below parser but gave me error 438, weird, i am getting crazy.Private Sub parsehtml() Dim http As Object, html As New HTMLDocument, document As Object Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", "https://bin.codingislove.com/ayequrimiy.json", False http.send http.getElementById("box").innerText = http.responseText MsgBox (http.responseText) End Sub – Leb_Broth Feb 01 '18 at 12:38
It is very hard to read code in comments, so my answer might be wrong. You must wait until the page is fully loaded before reading the DOM. Look at the ReadyState part in the example I mentionend above. – Sam Feb 01 '18 at 12:51
Yes I checked what you proposed in the console and it is working. Now I’ll try to make a simple snippet. Should I declare an internetexplorer object as to use the getelementbyid ? – Leb_Broth Feb 01 '18 at 13:02

score 1 · Accepted Answer · edited Feb 01 '18 at 10:49

Using developer tools with call to your second url https://bin.codingislove.com/ukiyerovow.json, it can be seen that the json is returned from url https://bin.codingislove.com/documents/ukiyerovow like this:

{
    "data":
        "{
            \"Count\":1,
            \"results\":
                [
                    {
                        \"showEmailIcon\":true,
                        \"showIcon\":true,
                        \"middleName\":\"\",
                        \"dateActivated\":1513000,
                        \"regAffiliateRebate\":\"No Rebate(0)\",
                        \"Id\":1,
                        \"dateLastLogin\":1513248842000,
                        \"countryName\":\"France\",
                        \"address\":null,
                        \"name\":\"cien\",
                        \"id\":1786511,
                        \"state\":null
                    }
                ],
            \"resultClass\":\"com.zoho.dao.dto\"
        }",
    "key":"ayequrimiy"
}

Using Json-vba library this strign can be parsed like this. HTH

Sub jsontest()
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    ' use this url instaed:
    Const url As String = "https://bin.codingislove.com/documents/ayequrimiy"
    http.Open "GET", url, False
    http.send

    Dim parsedJson As Dictionary
    Set parsedJson = JsonConverter.ParseJson(http.responseText)

    Dim data As String
    data = parsedJson("data")

    Dim parsedData As Dictionary
    Set parsedData = ParseJson(data)

    MsgBox parsedData("Count")
End Sub

What should I correct in my parser call?

You have to correct the url. The second url should be https://bin.codingislove.com/documents/ayequrimiy. There is the json data.

Compare:

To get e.g. Name you have to use the results which contains array so first point to the element of the array using index e.g. (1) and then take the element ("Name"):

Debug.Print parsedData("reports")(1)("Name")

but here is my main issue. I need in my parser call to convert somehow the .json url to the other url so I can use it. This was experimental but all the pages I need to parse are .json and I can’t simply transform them as text manually one by one. — Leb_Broth, Jan 30 '18 at 18:38
Did my answer answered your question? Is this another question you wrote in the comment? Or is the problem with the addition `data` element? — Daniel Dušek, Jan 30 '18 at 18:45
yes now I understood. So basically I should pass the url without the .json, but what if my files are in fact .json but their url isn’t showing the .json, what should I do in this case? — Leb_Broth, Jan 30 '18 at 18:47
If you try to parse more urls using the same code you will probably need to make some check for the `data` element. If `data` are present then use the extra step and get `data` as string like `parsedJson("data")`. The result is then the dictionary expected. Or maybe I do not understand what your problem is. — Daniel Dušek, Jan 30 '18 at 18:53
sorry about that. yes I replaced the url from where the json is coming and it is working fine. All i need now is to know from where all my json files are coming from. thanks a lot — Leb_Broth, Jan 30 '18 at 19:41
i Can successfully see all the response from “data” but how can I iterate in order to show only data from “totalCount” for example as I tried it and it shows empty value. — Leb_Broth, Jan 30 '18 at 19:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/164205/discussion-between-dee-and-leb-broth). — Daniel Dušek, Jan 30 '18 at 19:49

Issue with JsonConverter

2 Answers2

Linked