Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I would like to ask for your help regarding on my code, I am trying to convert Excel into Json file and I am having/encountering error variable is not define,Can someone tell me what is missing, may be Reference or missing declaration?

Below is my code:

Sub CreateJSONFile()
Dim jsonItems As New Collection
Dim jsonDictionary As New Dictionary
Dim jsonFileObject As New FileSystemObject
Dim jsonFileExport As TextStream
Dim i As Long
Dim cell As Variant
Dim colNum As Integer
Dim excelRange As Range
Set wsDst = ThisWorkbook.Sheets("Template")
Set excelRange = Cells(1, 1).CurrentRegion
colNum = 2
For i = 2 To excelRange.Rows.Count
    If wsDst.Cells(8, colNum) <> "" And wsDst.Cells(7, colNum + 1) = "" Then
        jsonDictionary("Object") = wsDst.Cells(8, colNum)
        jsonDictionary("Xpath") = wsDst.Cells(9, colNum)
        jsonDictionary("height") = wsDst.Cells(10, colNum)
        jsonDictionary("background-color") = wsDst.Cells(11, colNum)
        jsonDictionary("width") = wsDst.Cells(12, colNum)
        jsonDictionary("font-family") = wsDst.Cells(13, colNum)
        jsonDictionary("font-size") = wsDst.Cells(14, colNum)
        jsonDictionary("font-weight") = wsDst.Cells(15, colNum)
        jsonDictionary("font-style") = wsDst.Cells(16, colNum)
        jsonDictionary("font-stretch") = wsDst.Cells(17, colNum)
        jsonDictionary("line-height") = wsDst.Cells(18, colNum)
        jsonDictionary("letter-spacing") = wsDst.Cells(19, colNum)
        jsonDictionary("color") = wsDst.Cells(20, colNum)
        jsonItems.Add jsonDictionary
        Set jsonDictionary = Nothing
    End If
    colNum = colNum + 1
Next i
MsgBox JsonConverter.ConvertToJson(jsonItems, Whitespace:=3)
Set jsonFileExport = jsonFileObject.CreateTextFile(ThisWorkbook.Sheets("Template").Cells(1, 2).Value & "\" & ThisWorkbook.Sheets("Template").Cells(7, 2).Value & ".json", True)
jsonFileExport.WriteLine (JsonConverter.ConvertToJson(jsonItems, Whitespace:=3))
Set wsDst = Nothing
End Sub
                Wheee is JsonConverter declared and instantiated? If it hasn’t been instantiated, it’s a nothing object.
– Skin
                May 23, 2019 at 2:23
                It is an entire module of code that is missing and likely also a missing reference. The code is from here: github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas   and after adding to your project you need to also go VBE > Tools > References > Add reference to Microsoft Scripting Runtime.
– QHarr
                May 23, 2019 at 2:33
                @Toshi this can be resolved, as outlined in this comment. You need the module .bas file from the link mentioned, as well as a the reference to Microsoft Scripting Runtime.
– BigBen
                May 23, 2019 at 2:50
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.