VBA解析JSON数据（5）--JavaScript回写Excel

在上一篇博客【VBA解析JSON数据（4）–JavaScript进阶】中利用JavaScript代码实现读取JSON数据KEY和VALUE的功能，但是还有些繁琐。JavaScript代码读取，然后在VBA去写入，其实JavaScript也是很强大的，完全可以直接操作Exce单元格写入数据。

示例JSON数据如下所示。

{
      "applySn": "粤G妆网备字2017050113",
      "applySnTT": "",
      "apply_enter_address": "",
      "enterpriseName": "广州市金日精细化工有限公司",
      "enterpriseNameTT": "",
      "is_off": "N",
      "newProcessid": "20170421153208vq3fu",
      "off_date": "",
      "org_name": "",
      "processid": "20170421153208vq3fu",
      "productName": "悠莅雅柔美亮肤按摩霜",
      "productNameTT": "",
      "provinceConfirm": "2017-05-17"
}

示例代码如下。

Sub JSONToRange()
    Dim strJSON As String
    Dim strJSCode As String
    strJSON = [a1]
    strJSCode = "var json,key,col=row=1,d={};for(json in js){col++;for(key in js[json]){if(!d[key]){d[key]=row;ExCell(row++,1)=key;}ExCell(d[key],col)=js[json][key];}}"
    strJSCode = "js=[" & strJSON & "];" & strJSCode
    Range("A3:B3").Value = Array("Name", "Value")
    With CreateObject("ScriptControl")
        .Language = "JScript"
        .AddObject "ExCell", Cells(4, 1)
        .Eval (strJSCode)
    End With
End Sub

代码解析
第4行代码读取工作表中的JSON数据。
第5行和第6行代码为JavaScript代码。

为了简化代码，VBA代码中使用了JavaScript Minify格式，格式化的JavaScript代码如下：

var json, key, col = row = 1,
    d = {};
for (json in js) {
    col++;
    for (key in js[json]) {
        if (!d[key]) {
            d[key] = row;
            ExCell(row++, 1) = key;
        }
        ExCell(d[key], col) = js[json][key];
    }
}

第8行代码使用后期绑定的方式创建ScriptControl对象，如果使用前期绑定方式，需要在VBE中先引用Microsoft Script Control 1.0。
第9行代码指定ScriptControl对象的语言为JavaScript。
第10行代码添加名称为ExCell的对象，指向[A4]单元格。
第11行代码执行JavaScript代码。

运行示例代码，结果直接写入Excel工作表指定区域，相比上一个示例，代码简洁了许多。

---

相关博文链接：
VBA解析JSON数据（1）-- Split函数
VBA解析JSON数据（2）–正则表达式
VBA解析JSON数据（3）–JavaScript
VBA解析JSON数据（4）–JavaScript进阶
VBA解析JSON数据（5）–JavaScript回写Excel