VBA JsonParser clsJsonParser does not work

I have built a spider with scrapy. I run it and have the output in the json file as shown below. Then I am using clsJsonParser in a VBA, with the following code. But I am getting an 3265 error "element not found in this collection" for element.item("newstxt"); while the element.item("newstitle") works fine. What is going wrong? is it my VBA code, or the format of my json file?

Public Sub JSONImport()
Dim coll As Collection
Dim json As New clsJSONparser
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim element As Variant

Dim FileNum As Integer
Dim DataLine As String, jsonStr As String

' READ FROM EXTERNAL FILE
FileNum = FreeFile()
Open "C:\Users\Philippe\sfo\unepinquiry\items.json" For Input As #FileNum

' PARSE FILE STRING
jsonStr = ""
While Not EOF(FileNum)
    Line Input #FileNum, DataLine

    jsonStr = jsonStr & DataLine & vbNewLine
Wend
Close #FileNum

    Set db = CurrentDb
    Set rs = db.OpenRecordset("News_1", dbOpenDynaset, dbSeeChanges)
    Set coll = json.parse(jsonStr)

    For Each element In coll
        rs.AddNew
        rs!newstitle = element.item("newstitle")
        rs!newstxt = element.item("newstxt")
        rs.Update
    Next


Set element = Nothing
Set coll = Nothing

End Sub

[{"newstxt": ["On June 21, 2016, the fourth meeting of the G20 Green Finance Study Group was held in Xiamen. The meeting was hosted by Co-chairs from the People's Bank of China and the Bank of England. Delegates from G20 countries, invited guest countries and International Organizations participated in the meeting. The delegates discussed and agreed in principle on the G20 Green Finance Synthesis Report, which would be submitted to the June G20 Finance and Central Bank Deputies Xiamen Meeting. The study group will further revise and submit the Report to the July G20 Finance Ministers and Central Bank Governors Chengdu Meeting."], "newstitle": "\nFourth Meeting of the G20 Green Finance Study Group Concludes in Xiamen\n"}, {"newstxt": ["Mumbai, 29 April 2016\u00a0- India has set ambitious goals for inclusive and sustainable development, which require the mobilization of additional low-cost, long-term capital. A new report launched today by the United Nations Environment Programme (UNEP) and the Federation of Indian Chambers of Commerce and Industry (FICCI) shows how the country is already introducing innovative approaches to attract private capital for green assets - and outlines a number of key steps to deepen this process in India."], "newstitle": "\nNew Report Shows How India Can Scale up Sustainable Finance\n"}]

Can't tell, but if you run this test function:

Public Sub TestJsonText()

    Dim DataCollection      As Collection
    Dim ResponseText        As String

    ResponseText = _
        "[{""newstxt"": [""On June 21, 2016, the fourth meeting of the G20 Green Finance Study Group was held in Xiamen. The meeting was hosted by Co-chairs from the People's Bank of China and the Bank of England. Delegates from G20 countries, invited guest countries and International Organizations participated in the meeting. The delegates discussed and agreed in principle on the G20 Green Finance Synthesis Report, which would be submitted to the June G20 Finance and Central Bank Deputies Xiamen Meeting. The study group will further revise and submit the Report to the July G20 Finance Ministers and Central Bank Governors Chengdu Meeting.""]," & _
        """newstitle"": ""\nFourth Meeting of the G20 Green Finance Study Group Concludes in Xiamen\n""}, " & _
        "{""newstxt"": [""Mumbai, 29 April 2016\u00a0- India has set ambitious goals for inclusive and sustainable development, which require the mobilization of additional low-cost, long-term capital. A new report launched today by the United Nations Environment Programme (UNEP) and the Federation of Indian Chambers of Commerce and Industry (FICCI) shows how the country is already introducing innovative approaches to attract private capital for green assets - and outlines a number of key steps to deepen this process in India.""]," & _
        """newstitle"": ""\nNew Report Shows How India Can Scale up Sustainable Finance\n""}]"
    If ResponseText <> "" Then
        Set DataCollection = CollectJson(ResponseText)
        MsgBox "Retrieved" & Str(DataCollection.Count) & " root member(s)", vbInformation + vbOKOnly, "Web Service Success"
    End If

    Call ListFieldNames(DataCollection)

    Set DataCollection = Nothing

End Sub

using the Json modules found here: VBA.CVRAPI

it will print:

root                        
    0                       
        newstxt             On June 21, 2016, the fourth meeting of the G20 Green Finance Study Group was held in Xiamen. The meeting was hosted by Co-chairs from the People's Bank of China and the Bank of England. Delegates from G20 countries, invited guest countries and International Organizations participated in the meeting. The delegates discussed and agreed in principle on the G20 Green Finance Synthesis Report, which would be submitted to the June G20 Finance and Central Bank Deputies Xiamen Meeting. The study group will further revise and submit the Report to the July G20 Finance Ministers and Central Bank Governors Chengdu Meeting.
        newstitle           
Fourth Meeting of the G20 Green Finance Study Group Concludes in Xiamen

    1                       
        newstxt             Mumbai, 29 April 2016 - India has set ambitious goals for inclusive and sustainable development, which require the mobilization of additional low-cost, long-term capital. A new report launched today by the United Nations Environment Programme (UNEP) and the Federation of Indian Chambers of Commerce and Industry (FICCI) shows how the country is already introducing innovative approaches to attract private capital for green assets - and outlines a number of key steps to deepen this process in India.
        newstitle           
New Report Shows How India Can Scale up Sustainable Finance

which looks correct to me. So it is probably clsJSONparser you must investigate.

Recommended topics

Hot tags