Why does this JSON Microsoft Script VBA code work in Excel 2016 but takes forever in 2019?

[Indominus]

I have this JSON Microsoft Scripting VBA code that pulls data from a work website into Excel. Can pull about 55,000 rows of data. About 5 columns of data. On 2016 the code worked fine but on 2019, which I need the code on, it takes forever to run or crashes Excel. I then have to quit Excel. I even upped my RAM and it did not help. How can this be edited to work? I even have Ludicrous mode enabled to turn off calculations, events, etc. Thank you in advance.

Private Sub Pull_Shipments()

   Call LudicrousMode(True)

    Dim obj_http As Object, Json As Object, JSON1 As Object, x64 As Object, s As Object, key, keyring As Object, arr(),
 JSON2, SCC_Response As Object,
    inner_key, inner_keyring

    Set obj_http =

  CreateObject("WinHTTP.WinHTTPRequest.5.1")

    Set s =
    CreateObject("ScriptControl")
    s.Language = "JScript"
    s.AddCode "function keys(O) {
    var k = new Array(); for (var
    x in O) { k.push(x); } return k; } "


  Vrid_col = 3

  site = UCase(Sheets("Main").Range("B30"))

  'Reset info
   With Sheets("Shipments")
Row = 2
.Range("A:H").ClearContents

'set headers
.Cells(1, 1) = "Truck"
.Cells(1, 2) = "Tracking ID"
.Cells(1, 3) = "State"
.Cells(1, 4) = "Size"
.Cells(1, 5) = "Area"
.Cells(1, 6) = "Section"
.Cells(1, 7) = "Date"
.Cells(1, 8) = "Cycle"

'Set URLs
SC_URL = "work website "
API_URL = "work website"

'request SCC Get
obj_http.Open "GET", SC_URL
obj_http.SetAutoLogonPolicy 0
obj_http.setRequestHeader "Food", FoodJar
obj_http.send
obj_http.WaitForResponse

For line_haul = 2 To Application.CountA(Sheets("Site Trucks").Columns(2 + Vrid_col))
    haul_id = Sheets("Site Trucks").Cells(line_haul, 1 + Vrid_col)
    package_body = "{""resourcePath"":""/ivs/getPackageList"",""httpMethod"":""post"",""processName"":""induct"",""requestBody"":{""nodeId"":""" & site & """,""Truck"":""" & haul_id & """,""status"":""ALL"",""filters"":{""Cycle"":[],""Date"":[],""OtherAttributes"":[],""Section"":[],""Size"":[]}}}"
 
    'Post APU request
    obj_http.Open "POST", API_URL
    obj_http.setRequestHeader "Cookie", CookieJar
    obj_http.SetClientCertificate "CURRENT_USER\MY\" & Environ("USERNAME")
    obj_http.SetAutoLogonPolicy 0
    obj_http.setRequestHeader "Accept", "*/*"
    obj_http.setRequestHeader "Content-Type", "application/json"
    obj_http.send package_body
 
    Set SCC_Response = s.Eval("(" & obj_http.responseText & ")")
    Set Json = CallByName(SCC_Response, "packageList", VbGet)
    Set keyring = s.Run("keys", Json)

        'parse vehicle info
        For Each key In keyring
            .Cells(Row, 1) = haul_id
            .Cells(Row, 2) = CallByName(CallByName(Json, key, VbGet), "ID", VbGet)
            .Cells(Row, 3) = CallByName(CallByName(Json, key, VbGet), "state", VbGet)
            .Cells(Row, 4) = CallByName(CallByName(Json, key, VbGet), "size", VbGet)
            .Cells(Row, 6) = CallByName(CallByName(Json, key, VbGet), "section", VbGet)
            .Cells(Row, 7) = CallByName(CallByName(Json, key, VbGet), "date", VbGet)
            .Cells(Row, 8) = CallByName(CallByName(Json, key, VbGet), "cycle", VbGet)
      
            If CallByName(CallByName(Json, key, VbGet), "Area", VbGet) <> "" Then
                .Cells(Row, 5) = Split(CallByName(CallByName(Json, key, VbGet), "Area", VbGet), ".")(0)
            End If
            Row = Row + 1
        Next key
 
Next line_haul
End With

 Call LudicrousMode(False)

End Sub

 

[Indominus]

Nope - that is definitely not it. The JSON string will have all the data that it is you're populating the spreadsheet with. And that doesn't have any data in it.
From the code above, I know, for example, that it the JSON string wil have the words packageList, ID, State, Size,Section, Date, Cycle etc etc.

.Cells(Row, 1) = haul_id
            .Cells(Row, 2) = CallByName(CallByName(Json, key, VbGet), "ID", VbGet)
            .Cells(Row, 3) = CallByName(CallByName(Json, key, VbGet), "state", VbGet)
            .Cells(Row, 4) = CallByName(CallByName(Json, key, VbGet), "size", VbGet)
            .Cells(Row, 6) = CallByName(CallByName(Json, key, VbGet), "section", VbGet)
            .Cells(Row, 7) = CallByName(CallByName(Json, key, VbGet), "date", VbGet)
            .Cells(Row, 8) = CallByName(CallByName(Json, key, VbGet), "cycle", VbGet)
     
            If CallByName(CallByName(Json, key, VbGet), "Area", VbGet) <> "" Then

Your JSON string has none of those things. I feel we're almost there.

Just tried again and that obj.http.responseText is producing that. Maybe it’s the key?

 

[Dan_W]

JSONString = obj_http.responseText
Debug.Print JSONString

I suspect so - seeing as that's exactly what my code sought to extract. But you said it produced erors for some reason - which I can't fix until I see code, but anyway, I got to go. Good luck.

 

[Indominus]

I suspect so - seeing as that's exactly what my code sought to extract. But you said it produced erors for some reason - which I can't fix until I see code, but anyway, I got to go. Good luck.

Yeah I’m not sure what to do. I debugged that multiple times, key, keyring. Nothing is showing all at once

 

[Indominus]

I suspect so - seeing as that's exactly what my code sought to extract. But you said it produced erors for some reason - which I can't fix until I see code, but anyway, I got to go. Good luck.

Hi. I am still trying to figure this out. I watched a tutorial video of JSON Converter and understand it bette tone. However the JSON string that obj_http.responseText picks up really is {"packageList":[]}

Not sure how how to pull up the rest.

 

[Dan_W]

But it still runs and it still populates the spreadsheet?

 

[Indominus]

But it still runs and it still populates the spreadsheet?

Yes it does. The full code works it just takes forever right now. I am not sure why the full JSON string is not coming up with that response text. Maybe it has to do with the package_body line?

 

[Dan_W]

Well, that means that JSON string is there, because your Excel isn't making it up. The only thing it's downloading is obj_http.responseText. So it has to be that. Other than that, I'm not guess what the solution if you're not going to share the code. Good luck.

 

[Indominus]

I

Well, that means that JSON string is there, because your Excel isn't making it up. The only thing it's downloading is obj_http.responseText. So it has to be that. Other than that, I'm not guess what the solution if you're not going to share the code. Good luck.

I did share the code. Not sure what you mean.

 

[Dan_W]

I asked a while ago (Post 18) if you could please repost the updated code.

Can you repost the updated code here please?

The updated code was meant to include the debugging lines of code to try and identify the actual JSON String. Instead, you reported back that:

When I add that line and I get an error for “invalid procedure call or argument” on food and foodjar line

Which is not possible if you put in the lines of code where I suggested. So, again, I can't help you if you refuse to post the code. Perhaps someone else can help you.

 

[Indominus]

I asked a while ago (Post 18) if you could please repost the updated code.

The updated code was meant to include the debugging lines of code to try and identify the actual JSON String. Instead, you reported back that:

Which is not possible if you put in the lines of code where I suggested. So, again, I can't help you if you refuse to post the code. Perhaps someone else can help you.

I told you I fixed that error. I put in the same exact lines you told me to and I reported back what I got. It is is not working.