populateJSON acting very slowly...

[stu.mc...@gmail.com]

Hello,

I am using the following function from your cDataSet workbook example, with a few changed params.

dSet.populateJSON jo.deSerialize(cb.httpGET(url)).child("entry"), Range("import!$a$1")

Do you know why it might be taking so long? It takes 4-5 minutes to parse 2000 rows x 10 columns into Excel. The JSON from my URL is one massive dump in a single line. It starts populating rows immediately which I can visibly see (changing Application.ScreenUpdating doesnt change anything) but takes a long time to complete. Is this normal/expected?

Otherwise this is a brilliant function - perfect for what I need so Thanks!!!

Cheers

stu

[Bruce Mcpherson]

. did you turn calculate to manual from automatic ? 

. also to encourage the garbage collector between runs for a big dataset like this, you probably need to execute tearDowns() (http://ramblings.mcpher.com/Home/excelquirks/snippets/scope/garbage) , otherwise the memory wont be released till you exit excel

....something like (im just typing this into mail, so there may be some syntax problems..)

with cb

  with JSONParse (.httpGET(url) )

     dSet.populateJSON (.child("entry"), Range("import!$a$1")).teardown()

     .tearDown()

  end with

  .tearDown()

end with

bruce

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[stu.mc...@gmail.com]

Perfect, Thanks Bruce.

Here is my whole solution in case others want to use it:

Public Sub extractJson()

    Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject, url As String

    On Error GoTo extractJson_err

    

    'get the url from a named cell range, switch to import sheets

    url = Range("lenniesJsonUrl").Value2

    Sheets("import").Select

    

    'Application.ScreenUpdating = False

    Application.Calculation = xlManual

    

    'fetch the Json from the url, parse it from the object name (entry) and populate the specified range

    With cb

        With JSONParse(.httpGET(url))

            dSet.populateJSON(.child("entry"), Range("import!$a$1")).tearDown

            .tearDown

        End With

        .tearDown

    End With

extractJson_clean:

    Application.ScreenUpdating = True

    Application.Calculation = xlAutomatic

    Exit Sub

extractJson_err:

    MsgBox "Error: " & vbCrLf & vbCrLf & Err.description

    Err.clear

    Resume extractJson_clean

End Sub