How to access JSON object returned from createObject("htmlfile") in VBS file

Question

I have the following json:

{
   "columns":[
      "b_summary",
      "end_fy",
      "id",
      "isin",
      "market",
      "name",
      "num_employees",
      "sector",
      "ticker"
   ],
   "data":[
      [
         "Microsoft Corp is a technology company. ",
         6,
         59265,
         "US5949181045",
         1,
         "MICROSOFT CORP",
         166475,
         101003,
         "MSFT"
      ]
   ]
}

I've parsed the text using this:

Function parseJson(strJson)
    dim html, window
    Set html = wscript.CreateObject("htmlfile")
    Set window = html.parentWindow
    window.execScript "var json = " & strJson, "JScript"
    Set parseJson = window.json 
End Function

Dim JSON
Set JSON = ParseJson(jsonText)
wscript.echo JSON.columns.length & " columns"
wscript.echo JSON.data.length & " rows"

I can see:

9 columns
1 rows

but I can't see how to access any of the properties under those.

I've tried:

JSON.columns(1), JSON.columns("1"), JSON.columns("[1]"), JSON.columns(1).name, JSON.columns(1).value

which resulted in "Unknown Runtime Error"

And

JSON.columns.item(1), JSON.columns.items(1)

And

dim col
for each col in JSON.columns

which resulted in "Object doesn't support this property or method."

I loaded it with vbsEdit, and it can see the json items listed neatly in their Variable Watcher during runtime, but I don't know how I can access them from my script.

Any idea how to access those properties will be appreciated!

LesFerch · Accepted Answer · 2023-07-08 00:59:44Z

1

There appears to be no way to reference the JSON array elements using VBScript with the MSHTML JSON parser. One option is to rewrite that part of the script in JScript:

function parseJSON(strJSON) {
  var oHTML = new ActiveXObject("htmlfile");
  var window = oHTML.parentWindow;
  window.execScript("var JSON = " + strJSON, "JScript");
  return window.JSON;
}

var oFSO = new ActiveXObject("Scripting.FileSystemObject");
var oFile = oFSO.OpenTextFile("Test.JSON");
var JSONText = oFile.ReadAll();
oFile.Close();
var oJSON = parseJSON(JSONText);
WScript.Echo(oJSON.columns[0]);
WScript.Echo(oJSON.columns[1]);

Another option is to use the JSON object (Set parser = CreateObject("vbsedit.json")) from the VbsEdit Toolkit. The example included with VbsEdit shows how to reference each array element via a For Each loop, but I did not see an example showing how to directly use an index.

edited Jul 8, 2023 at 0:59

answered Jul 8, 2023 at 0:48

LesFerch

1,9972 gold badges8 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alfadog67 Over a year ago

Replacing vbscript with jscript has allowed me to download, parse, save as csv and upload to my database in one single script! Thanks again!

Collectives™ on Stack Overflow

How to access JSON object returned from createObject("htmlfile") in VBS file

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related