jsonvbadictionaryobjectms-access

How to get an item value from JsonObject


I have a couple of json-files which I need to transform into .xml-files. json - structure is given. When I try to read an item value of the jsonbject, I got an error "types incompatible" when depug.print (Debug.Print Item("GSID")) respectively when I try to set value to a string variable.

My code:

Dim strFooter, strHeader As String
    Dim fso As FileSystemObject
    Dim strFileContent As String
    Dim strFile, strFilePath, strFilePathInput As String
    Dim srtVat, strCompanyID, strCompanyName As String
    Dim xmlFile As Object
    Dim jsonFileInput As TextStream

Set jsonFileInput = fso.OpenTextFile(strFilePathInput & strFile, ForReading)
strFileContent = jsonFileInput.ReadAll
Set jsonFile = JsonConverter.ParseJson(strFileContent)
For Each Item In jsonFile("CRM").Item("Header")
    Debug.Print Item("GSID")
Next Item

here is the json

{
    "CRM": {
        "Header": {
            "ID": "08803",
            "GSID": "1475",
            "MessageID": "0066M00001X3xwvQAB8242",
            "SenderID": "Systema",
            "PayloadVersionID": "1.0"
        },....

I need a couple of values out of the json files to write these into a new xml-file.

thanks in advance!


Solution

  • Iterating over root/CRM/Header would be more like this:

    Dim dict As Object, k
    
    '...
    '...
    
    Set dict = jsonFile("CRM")("Header")
    For Each k In dict
        Debug.Print k, dict(k)
    Next k