Home Finding new elements in JSON array and update spreadsheet
Reply: 0

Finding new elements in JSON array and update spreadsheet

Leb_Broth
1#
Leb_Broth Published in 2018-02-14 17:59:40Z

I am parsing my JSON list everyday to my Excel spreadsheet. It is a list of around 400-500 user info and everyday it is being updated. Now because i don't want to parse the same list everyday, i just need to locate the new objects that are added to the JSON and comparing it with the existing parsed list in Excel. New objects will be added if not found. This is my code below:

Private Sub populate_Click()

Dim http As Object, JSON As Object, Item As Variant
Dim i As Integer

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://controlpanel.verio/rest/reports?errorMessages=null", False
http.send

Dim jsnStr As Object
Set jsnStr = ParseJson(http.responseText)
'Fetching data
i = 3
For Each Item In jsnStr("results")(1)("results")

    Dim findy As Variant
    findy = Item(4)
    Columns("B:B").Select
    Set cell = Selection.Find(What:=findy, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If cell Is Nothing Then
    'do it something
    Sheets(1).Cells(i, 1).Value = Item(3)
   Sheets(1).Cells(i, 2).Value = Item(4)
   Sheets(1).Cells(i, 5).Value = Item(18)
   Sheets(1).Cells(i, 6).Value = Item(6)
   Sheets(1).Cells(i, 7).Value = Item(7)
   Sheets(1).Cells(i, 8).Value = Item(8)
   Sheets(1).Cells(i, 9).Value = Item(5)
   Sheets(1).Cells(i, 11).Value = Item(20)

    i = i + 1
    Else
    'do it another thing
    End If

Next
Set JSON = Nothing
Set http = Nothing

End Sub

I am not receiving any error but nothing is changed or updated from the excel list.

What should I correct in the below code?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302384 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO