0

We are upgrading a legacy spreadsheet application to run in Excel 365 64 bit, win 10. The spreadsheet application interacts with a RESTful API through HTTP post, the response JSON is parsed leveraging "MSScriptControl.ScriptControl".

The spreadsheet application does not run in Excel 365 64 bit because the underlying "microsoft scripting runtime" (scrrun.dll) is a 32 bit file and is not supported natively in Excel 365, win 10. We have looked at various solutions like https://github.com/VBA-tools/VBA-JSON however this solution still makes use of the scripting runtime and fails. We considered also looking at alternative scripting runtimes such as https://github.com/tablacus/TablacusScriptControl but we would not be able to release the new scripting runtime on client machines.

The code below illustrates what we are currently doing in our VBA code:

    Set request = CreateObject("MSXML2.XMLHTTP")
    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")

    request.Open "post", custom_url, False
    jsonRequest = "..."
    request.Send (jsonRequest)

    If request.Status = 200 Then

        scriptControl.Language = "JScript"
        scriptControl.AddCode "function GetPropertyValue(jsonObj, propertyName) { return jsonObj[propertyName]; } "

        Set json = scriptControl.Eval("(" + request.ResponseText + ")")

    End If

We have searched for a simple solution online but we cannot find one. How do you parse JSON in Excel 365 VBA without having to write a custom JSON parser? Are there any MS equivalent libraries to MSScript control that can be used in Excel 365 64 bit?

SharpRock
  • 11
  • 3
  • This is what I use to parse Json, check if it helps: https://github.com/VBA-tools/VBA-JSON – Damian Oct 23 '19 at 17:11
  • 1
    "however this solution still makes use of the scripting runtime and fails". You'll have to back that claim, because VBA-JSON is portable by design and works perfectly fine on a Mac (which doesn't have the scripting runtime library). From [readme](https://github.com/VBA-tools/VBA-JSON/blob/master/README.md): **For Mac and Windows support, include [VBA-Dictionary](https://github.com/VBA-tools/VBA-Dictionary)** – Mathieu Guindon Oct 23 '19 at 17:12
  • 1
    Scripting runtime library should work perfectly fine in a 64-bit host; the ScriptControl ActiveX control is the problem (won't work in a 64-bit host), and this is true of any 32-bit ActiveX control (e.g. date picker). The problem is the 32-bit ActiveX control, not the scripting runtime. – Mathieu Guindon Oct 23 '19 at 17:22
  • Try [this solution](https://stackoverflow.com/a/38134477/2165759), although I would avoid parsing JSON with ScriptControl. – omegastripes Jan 04 '20 at 23:27

0 Answers0