Import JSON data into a Microsoft Word template?

Question

I am currently working on an application that grabs information from an API and returns it as JSON. The purpose of all the data is to fill in a Microsoft Word document that stays the same every time, but the information changes. I have discovered 'Winward Studios' product which allows you to create a template document in Word and basically plug in the spots where the information will be pulled from the JSON, and specify the keys. However, it is very expensive ($261 a month). I currently have the users download the JSON from a web page and overwrite an existing file that is connected to the Word template. Is there any alternative products/ways to accomplish this?

Answer 1

1) Word ( and excel, probably others as well ) are a form of XML. So my first try was creating a word document. That way we would be able to just concat the JSON data. This was too difficult for me.

2) The second try was an excel document embedded inside the word document. Excel has a Data tab, which allows you to import from JSON in modern versions. In older versions that do not support importing JSON directly, I just imported from web and provided the JSON string as the body of a HTML document. I guess you're currently doing something similar. This worked, but felt very subpar.

3) In the end, I just wrote an ajax function and a accessor in VBA so we could use them in old versions of excel and word with macros.

' Provide URL to a resource, get parsed JSON version back.
' XMLHttpRequest() is native as MSXML2.XMLHTTP
' JScript was the MS implementation of javascript.
' Since JSON is valid javascript, the JScript parser can eval our JSON string
' Caveat: we can't directly access the values yet, so use getJSVal() to get the values.
' Caveat: no authentication on the request.
Public Function ajax(endpoint As String) As Object
    Dim HTTP As Object
    Dim parser As Object
    Dim json As Object
    Set HTTP = CreateObject("MSXML2.XMLHTTP")
    Set parser = CreateObject("MSScriptControl.ScriptControl")
    HTTP.Open "GET", endpoint, False
    'extra headers the service expects
    'HTTP.setRequestHeader "x-header-name", "header-value"
    HTTP.send
    parser.Language = "JScript"
    Set json = parser.Eval("(" + HTTP.responseText + ")")
    HTTP.abort
    Set ajax = json
End Function

And

' Grab an object by key name from the JS object we loaded and parsed.
' We can't access this directly without installing a parser that converts the JSON to actual variables.
' See the Microsoft Office App Store for the official free JSON-To-Excel converter
Public Function getJSVal(json As Object, key As String) As Variant
    Dim result As Variant
    result = VBA.CallByName(json, key, VbGet)
    If IsNull(result) Then
        getJSVal = 0#
    Else
        getJSVal = result
    End If
End Function

Once we update our old office versions, we will start using one of the official JSON parsers for Office.