0

We have a VBA script that runs in Excel 32bit to connect to an API, and convert a JSON string.

However when we try to run the same string on 64bit, the script fails with a “Class not registered” error. This relates to “Script Control” from the Microsoft Script Control 1.0

Private Sub TestJSONParsingWithCallByName()

Dim oScriptEngine As ScriptControl
Set oScriptEngine = New ScriptControl
oScriptEngine.Language = "JScript"

Dim sJsonString As String
sJsonString = "{'key1': 'value1'  ,'key2': { 'key3': 'value3' } }"


Dim objJSON As Object
Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")
Debug.Assert VBA.CallByName(objJSON, "key1", VbGet) = "value1"
Debug.Assert VBA.CallByName(VBA.CallByName(objJSON, "key2", VbGet), "key3", VbGet) = "value3"
 End sub

In fact our script was built basis the idea found here: Is There a JSON Parser for VB6 / VBA?

The issue is that we are about to convert our Office system from 32bit to 64bit. And this doesn’t work. we are unable to install any third party software to assist us solve this issue, but surely it must be solvable by using code within the 64bit environment.

Thank you

am.cosomo
  • 15
  • 6
  • As said in that answer, we don't have a 64-bit script control. However, there are plenty of VBA-only JSON parsers that don't require any installation of software – Erik A Feb 14 '20 at 08:14
  • Like what for example ? – am.cosomo Feb 14 '20 at 08:30
  • The first answer on the question you linked, https://github.com/VBA-tools/VBA-JSON, and I've even developed one myself, https://github.com/erikvona/JSONInterpreter. The main difference between mine and others is mine tends to be a bit slower at parsing whole files, but allows partial parsing for incomplete or long files (stops when the requested value is found instead of parsing the whole file), and can be used to modify JSON in-place instead of converting to and from a dictionary. – Erik A Feb 14 '20 at 08:36
  • Another alternative, btw, is calling a 32-bit COM object from a 64-bit application using COM surrogate (DLLHost), but that requires tuning of the registry and if you can't install programs, you probably can't do that. – Erik A Feb 14 '20 at 08:43
  • yeah for sure, we will try to use different json interpreter then – am.cosomo Feb 14 '20 at 08:58
  • Try to check this answer at Microsoft: https://social.msdn.microsoft.com/Forums/en-US/c6e9c23f-a455-4138-ad86-954d95420739/excel-vba-compatibility-issues-with-microsoft-scriptcontrol-10?forum=isvvba – xShen Feb 14 '20 at 09:02

0 Answers0