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

 

[Dan_W]

Hi. When you say it takes forever to run, does that mean that - when it's not crashing - there are occasions when the code actually works?
I ask because there are a few things that jump out at me, but perhaps the one key element that draws my attention is the use of ScriptControl - I wonder if this is the cause of the problem. Do you happen to know whether or not you're using 32-bit or 64-bit Excel? (not to be confused with the operating system, which is more likely than not going to be 64-bit Windows).

I can't guarantee that it would run any quicker, but there is certainly another (arguably better/safer) way of doing what it is you're trying to do. I'm happy to help rewrite it for you, if you like. It's a little bit tricky to run tests, etc. though because I don't have the website details, etc so I'll wait to hear from you first.

 

[Indominus]

Hi. When you say it takes forever to run, does that mean that - when it's not crashing - there are occasions when the code actually works?
I ask because there are a few things that jump out at me, but perhaps the one key element that draws my attention is the use of ScriptControl - I wonder if this is the cause of the problem. Do you happen to know whether or not you're using 32-bit or 64-bit Excel? (not to be confused with the operating system, which is more likely than not going to be 64-bit Windows).

I can't guarantee that it would run any quicker, but there is certainly another (arguably better/safer) way of doing what it is you're trying to do. I'm happy to help rewrite it for you, if you like. It's a little bit tricky to run tests, etc. though because I don't have the website details, etc so I'll wait to hear from you first.

Hi. So I have been trying trial and error literally the whole day and I cannot find the root cause of this. So yes, it does run but can take over 20 minutes to hours. During this time I cannot do anything on Excel so I just Force Close through Task Manager. I have gotten it to run in about 50 seconds a couple of times which is the quickest but then I retry it here and there using the same steps but I lose the consistency. I have tried different formats and removing all my other data from the workbook. Initially I thought I had it after starting a fresh file. It worked a couple of times fast but now it will not. Also every time the laptop goes idle the code seems to finish. Whereas if I have it open I really never see it finish.

I am on Excel 64bit with Tablacus Script Control 64 installed. I tried it the other day on 32bit but ended with the same issue. Although I just ran the same code and didn’t try to mess with it.

I appreciate you willing to help. I’m so perplexed by this.

 

[Dan_W]

Ahh - Tablacus Script Control. Interesting. I've downloaded it, but haven't got around to installing it. I would need to look at the documentation for it, but I'm not sure that it's meant to be called with CreateObject("ScriptControl"). My gut reaction is that you are in fact calling the native MS script control (that we're being discouraged from using - especially those of us on 64 bit Excel), but I may be completely wrong on that. It's been a while for me to look at script control.

It's a bit perplexing that it sometimes works and that sometimes it doesn't, and when it does work, the time it takes to execute the script is variable. It might be helpful to set timers in the code to try and work out where the bottlenecks are - but that we might be able to bypass that altogether. I don't know yet.

In any event, I don't see that you need it. Your code uses ScriptControl to leverage it's ability to use JScript (a kind of early-Javascript) in order to parse a JSON string. In VBA, we don't need to do that anymore - someone (a genius) has written a library that is easy enough to use and available for free (https://github.com/VBA-tools/VBA-JSON). I can help you to implement into your code, but are you able to tell me a bit more about what the code does? Importantly, is it possible to share the JSON string so I can get a sense of the JSON structure?

 

[Dan_W]

I can see from the code that you make two API calls:

1. a GET request to a work website - is this an intranet system? I see also that it sets a request header called "Food", using a variable FoodJar, but I don't see anywhere in the code where data is being assigned to this variable. It's not entirely clear to me why you're making this call at all, though, because having made it, you don't seem to do anything with it.
2. a POST request to a work website - is this the same website? The URL is being stored in separate variables, but you've described them both as "work website".

When you make the call, does it produce 55,000 rows of data each time? Is that what likely consumes most of the time?

 

[Indominus]

Ahh - Tablacus Script Control. Interesting. I've downloaded it, but haven't got around to installing it. I would need to look at the documentation for it, but I'm not sure that it's meant to be called with CreateObject("ScriptControl"). My gut reaction is that you are in fact calling the native MS script control (that we're being discouraged from using - especially those of us on 64 bit Excel), but I may be completely wrong on that. It's been a while for me to look at script control.

It's a bit perplexing that it sometimes works and that sometimes it doesn't, and when it does work, the time it takes to execute the script is variable. It might be helpful to set timers in the code to try and work out where the bottlenecks are - but that we might be able to bypass that altogether. I don't know yet.

In any event, I don't see that you need it. Your code uses ScriptControl to leverage it's ability to use JScript (a kind of early-Javascript) in order to parse a JSON string. In VBA, we don't need to do that anymore - someone (a genius) has written a library that is easy enough to use and available for free (https://github.com/VBA-tools/VBA-JSON). I can help you to implement into your code, but are you able to tell me a bit more about what the code does? Importantly, is it possible to share the JSON string so I can get a sense of the JSON structure?

So I actually have JSONConverter in a module already in my workbook however, I am not sure how to use it. And this code pulls all the id numbers and some other data from supply trucks.

The food and foodjar part is to access the website since it is locked behind my work network. Need a password to access the network to get into it. The code for this is in another module. It is ran first in the main code.

So when it makes a call it does one cell at a time. Moves from left to right.

It’s just so weird how this is so different on 2019 version. Worked fine on 2016.

 

[Dan_W]

Well, were you on 64-bit Excel when using the 2016 version? I can't help but feel that it's the fault of scriptcontrol - I've tried calling it in a variety of tricky ways, and it's almost always stalled things for me.

That's great to hear that you've already onto JSON converter. I also struggled with working out how to make it work at first, but I've got a pretty good grasp on it now. I suspected that there was a lot of authentication/password issues in what you were doing, so it may be tricky, but I can certainly try to help.

 

[Indominus]

Well, were you on 64-bit Excel when using the 2016 version? I can't help but feel that it's the fault of scriptcontrol - I've tried calling it in a variety of tricky ways, and it's almost always stalled things for me.

That's great to hear that you've already onto JSON converter. I also struggled with working out how to make it work at first, but I've got a pretty good grasp on it now. I suspected that there was a lot of authentication/password issues in what you were doing, so it may be tricky, but I can certainly try to help.

So I was on both at times. It worked on 32-bit and it worked on 64-bit only with Tablacus Script Control though. Yes, this website has an extra security to it that makes it a bit tricky. So with JSONConverter in my workbook where do I go from here?

 

[Dan_W]

Need to implement it - so I need to understand what the JSON string that is returned to you looks like. Then can work out how to extract from that with JSONConverter.
Also, why is the first API call being made? You don't appear to do anything with it.

 

[Indominus]

Need to implement it - so I need to understand what the JSON string that is returned to you looks like. Then can work out how to extract from that with JSONConverter.
Also, why is the first API call being made? You don't appear to do anything with it.

So stepping through the code I do not see any JSON string in the VBA window? Also I am not seeing the APIs not doing anything. If I remove it I get an error