如何使用VBA/JS解析含空格键的JSON？附实现代码

Hey there! The issue you're hitting is super common: when JSON keys have spaces in them, VBA's CallByName can't handle them because it expects valid identifiers (no spaces allowed). But since you're already using the ScriptControl with JScript, we can extend that approach to work with those tricky keys easily.

Here's the Fix: Add a JScript Helper Function for Property Access #

JScript lets you access object properties using square brackets (obj["key with spaces"]), which is perfect for our case. We'll add a small helper function to your ScriptControl setup to leverage this.

Modified Full Code Example #

Sub ParseJSONWithSpaceKeys()
    Dim scriptCtrl As Object
    Dim jsonObj As Object
    Dim httpObj As Object ' Assume this is your existing HTTP object (e.g., MSXML2.XMLHTTP)
    
    ' Initialize ScriptControl with JScript
    Set scriptCtrl = CreateObject("ScriptControl")
    scriptCtrl.Language = "JScript"
    
    ' Add helper functions: get all keys, and get properties by name (supports spaces)
    scriptCtrl.AddCode _
        "function getObjectKeys(obj) { var keys = []; for (var k in obj) keys.push(k); return keys; }" & _
        "function getProperty(obj, key) { return obj[key]; }"
    
    ' Parse the raw JSON response
    Set jsonObj = scriptCtrl.Eval("(" & httpObj.responseText & ")")
    
    ' Now access keys with spaces using the helper function
    ' Example path: responses list -> [0] -> aggregations data -> [2] -> bucket items
    Set jsonObj = scriptCtrl.Run("getProperty", jsonObj, "responses list")
    Set jsonObj = scriptCtrl.Run("getProperty", jsonObj, "0") ' Array index works as string
    Set jsonObj = scriptCtrl.Run("getProperty", jsonObj, "aggregations data")
    Set jsonObj = scriptCtrl.Run("getProperty", jsonObj, "2")
    Set jsonObj = scriptCtrl.Run("getProperty", jsonObj, "bucket items")
    
    ' Bonus: Iterate through all keys (including those with spaces)
    Dim allKeys As Variant
    allKeys = scriptCtrl.Run("getObjectKeys", jsonObj)
    
    Dim key As Variant
    For Each key In allKeys
        Debug.Print "Key: '" & key & "' | Value: " & scriptCtrl.Run("getProperty", jsonObj, key)
    Next key
    
    ' Cleanup
    Set scriptCtrl = Nothing
    Set jsonObj = Nothing
End Sub

Key Details to Note #

1. The getProperty Function: This is the magic—it takes your JSON object and a string key (with spaces allowed) and returns the corresponding value using JScript's bracket notation.
2. Array Indices: You can pass array indices as strings (like "0" or "2") to getProperty too, so you don't have to switch between methods for arrays and objects.
3. Alternative for Fixed Paths: If your JSON structure is static, you can skip the helper function and directly evaluate the path in one line:

   Set jsonObj = scriptCtrl.Eval("(" & httpObj.responseText & ")['responses list'][0]['aggregations data'][2]['bucket items']")
   

   This is more concise but less flexible if you need dynamic key access.

Quick Note for 64-bit Office Users #

The standard ScriptControl might not work in 64-bit Office. If you run into issues, you can either:

• Use the 64-bit version of Microsoft Script Control (if available), or
• Switch to a pure VBA JSON parser like VBA-JSON (no external dependencies needed).

内容的提问来源于stack exchange，提问作者Matúš Porubčan