Can I use the Newtonsoft JSON .NET library with my Excel VBA Macro to convert JSON to XML using DeserializeXmlNode?

Question

I am trying to convert a JSON string to XML string in my excel macro, and I found out that Newtonsoft's json .net library has a DeserializeXmlNode method in JsonConvert (https://www.newtonsoft.com/json/help/html/convertingjsonandxml.htm) which I think would help me convert a JSON string to XML string - but I am not sure if I can use this library in MS Excel...

Again, I have a JSON string in a VBA variable, and I want to convert it into an XML string using DeserializeXmlNode method, and get the XML Data returned into another VBA variable and MsgBox the converted XML. Is this possible? How do I use the Newtonsoft JSON .NET library in my excel workbook? I am new to vba and I am unsure regarding this... Kindly advice... Thanks!

P.S. I read this topic How to use Newtonsoft library with an excel macro and in comments Tim says create a com dll - if that's going to help me, how do I do that?

Edit 2 : Here's how the updated JSON string with array looks like - (again this is a representation of my updated JSON, actually its very big)

{
  "metadata": {
    "refYear": 2022,
    "formName": "F3CB-3CD"
  },
  "data": {
    "FORM3CB": {
      "F3CB": {
        "Declaration": {
          "Point1": {
            "My_Our1": "Our",
            "Observations": [
              {
                "ObservationsCode": "01",
                "ObservationsVal": "Observation 1 "
              },
              {
                "ObservationsCode": "02",
                "ObservationsVal": "Observation 2"
              },
              {
                "ObservationsCode": "03",
                "ObservationsVal": "Observation 3"
              }
            ]
          }
        },
        "PartA": {
          "AssesseeName": {
            "Name": "Sample Name"
          }
        }
      }
    }
  }
}

"Create a dll" was an off-the-cuff suggestion: if you don't know how to do that (I don't either) then you might investigate deserializing the JSON using the VBA-JSON library linked in the other post, and mimic the NewtonSoft "create XML" functionality in VBA. Is your JSON formatted like the example on the linked page, or is it just "regular" JSON? — Tim Williams, Aug 23 '22 at 20:16
Hi @TimWilliams, thanks for replying... I'll have a look at the VBA-JSON library. About mimicking the newtonsoft's create XML functionality, how do I do that? Any ideas? Also my JSON is a pretty standard json... Thanks again! — kartik, Aug 23 '22 at 22:11
You might get more suggestions if you can share a full/valid example of the type of JSON you have. Basically you'd have to traverse the deserialized JSON (Dictionaries(s)/Collection(s)) and create an XML document based on the contents. That's likely not super-simple but still do-able if that's what you need to do. — Tim Williams, Aug 23 '22 at 22:12
@TimWilliams I've updated my original question with a representation of what my JSON string would look like. Please have a look... Thanks! — kartik, Aug 23 '22 at 22:22
And how should the equivalent XML look? There's no single "root" object in that JSON for example, and XML needs a single root element... — Tim Williams, Aug 23 '22 at 22:51

Tim Williams · Accepted Answer · 2022-08-24T18:50:41.817

EDIT: updated for slightly different array/collection handling.

Seemed interesting so I had a crack at it - very lightly tested...

Sub Tester()

    Dim json As Object, doc As New MSXML2.DOMDocument60
    Dim el As Object
    
    Set json = JsonConverter.ParseJson(Range("A1").Value) 'read json from cell
    
    Set el = doc.createElement("root") 'Need a root if your json doesn't
                                       ' have a single root element
    doc.appendChild el   'add the root node to the document
    
    ProcessItem doc, el, json 'start processing
    'replace tabs with spaces for disply in cell...
    Range("B1").Value = Replace(PrettyPrintXML(doc.XML), vbTab, "    ")
    Debug.Print PrettyPrintXML(doc.XML)

End Sub

Sub ProcessItem(doc As MSXML2.DOMDocument60, parentElement As Object, itm)
    Dim k, el As Object, e, elName, grandParent, i As Long
    Select Case TypeName(itm)  'what type of thing is `itm`?
        Case "Dictionary" 'an object: iterate the keys and process each value
            For Each k In itm
                Set el = doc.createElement(k)
                parentElement.appendChild el
                ProcessItem doc, el, itm(k)
            Next k
        Case "Collection" 'an array: process each element in the array
            Set grandParent = parentElement.ParentNode
            elName = parentElement.tagName
            For Each e In itm
                i = i + 1
                If i > 1 Then
                    Set el = doc.createElement(elName) 'create new element
                    grandParent.appendChild el 'append to grandparent
                Else
                    Set el = parentElement 'use existing element
                End If
                ProcessItem doc, el, e
            Next e
        Case Else 'not an object or an array - just a text node
            Set el = doc.createTextNode(itm)
            parentElement.appendChild el
    End Select
End Sub

'https://stackoverflow.com/a/1118577/478884
Public Function PrettyPrintXML(XML As String) As String
    Dim Reader As New SAXXMLReader60
    Dim Writer As New MXXMLWriter60
    
    With Writer
        .indent = True
        .standalone = False
        .omitXMLDeclaration = False
        .Encoding = "utf-8"
    End With
    With Reader
        Set .contentHandler = Writer
        Set .dtdHandler = Writer
        Set .errorHandler = Writer
        .putProperty "http://xml.org/sax/properties/declaration-handler", Writer
        .putProperty "http://xml.org/sax/properties/lexical-handler", Writer
        .Parse (XML)
    End With
    
    PrettyPrintXML = Writer.output
End Function

Input/output (with changes for handling array):

Thanks a ton Tim! It works perfectly for me... I just had one question : I've now been given an updated JSON to work with which has arrays like the ones you added in red colour in your json _(I have updated the JSON in my original question, please have a look)_. Now the thing is `Case "Collection"` adds 'item' for every array element it sees - what if I want to change it to something like this https://i.ibb.co/dJBRKFh/image.png? Please have a look on the image as I have explained more clearly there... Thanks again for your wonderful solution! :) — kartik, Aug 24 '22 at 11:23

Can I use the Newtonsoft JSON .NET library with my Excel VBA Macro to convert JSON to XML using DeserializeXmlNode?

1 Answers1