#Notice this is also posted on StackOverflow - Posted as a courtesy to Orange#
I have got the "parser" to work with this code. Just made sure I could access all the fields, but I am stuck here, I have no idea how to reference or loop each of the fields, arrays and nested arrays to use them in Excel/Access. I just made sure I could access all the fields
For example ("fields")("comment")("comments") has 140 objects how would I loop through those comments so I could populate an Access DB/Excel spreadsheet?
Code:
Sub getJSONEP_lib()
'###Using VBA-JSON 2.2.2
'(Import jsonconverter.bas from project here:
'https://github.com/VBA-tools/VBA-JSON
'files------ https://codeload.github.com/VBA-tools/VBA-JSON/zip/master
' ##--References - Microsoft Scripting Runtime-
'Assemble URL , url2 sets paramaters (search criteria)
URL ="https://jira.atlassian.com/rest/api/2/issue/JRA-9.json"
url2 ="[{"&"""mpn"""&":"&"""41202"""&"}]"
'Confirm URL Assembly
'Debug.Print URL
'Get JSON Data from HTTP Request
Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
MyRequest.Open "GET", URL
MyRequest.Send
'JSON text Now Available As String as [ResponseText] from WinHttp Object
JsonString = MyRequest.ResponseText
'Confirm JSON Data
' Debug.Print JsonString
'Parse jsonString
Dim JSON AsObject
Set JSON = JsonConverter.ParseJson(JsonString)
'Read Data from JSON (Json) and Disply
'Now Can Read Data Directly From JSON Parse (Need to Loop These)
'This works and returns some keys, then fails, not adrressing the nested arrays properly??
ForEach keyName In JSON.Keys
'MsgBox "Keyname=" & keyName & "//Value=" & JSON(keyName)
Next
'Got this from another post
'This returns the correct count of the indicies
Dim var AsObject
' Get the object from Json
Set var = JSON("fields")("comment")("comments")
' Both Dictionary and Collection support the Count property
Debug.Print var.Count
'This doesn't return anything but doesn't fail either
Dim elem AsVariant
x = elem
ForEach elem In var
'Debug.Print x
Next elem
'Confirm Data Presence
'Proof of Concept - Data can be parsed and read if returns these strings
'Can access arrays and nested arrays
ndent =(" ")
Root_expand = JSON("expand")
Root_id = JSON("id")
Root_self = JSON("self")
Root_key = JSON("key")
'Array (fixVersions)
First = JSON("fields")("fixVersions")(1)("self")
snd = JSON("fields")("fixVersions")(1)("id")
Third = JSON("fields")("fixVersions")(1)("description")
Fourth = JSON("fields")("fixVersions")(1)("name")
Fifth = JSON("fields")("fixVersions")(1)("archived")
FirstCommentID = JSON("fields")("comment")("comments")(1)("id")
LastCommentID = JSON("fields")("comment")("comments")(140)("id")
Emails = JSON("fields")("customfield_10150")(1)
LastField = JSON("fields")("comment")("startAt")
'Confirm Data Presence
'Proof of Concept - Data can be parsed and read if returns these strings
'Can access arrays and nested arrays
Debug.Print Root_expand
Debug.Print Root_id
Debug.Print Root_self
Debug.Print Root_fixVersions
Debug.Print Root_key
Debug.Print
Debug.Print "Start of array in ROOT - fixVersions"
Debug.Print
Debug.Print ndent &" self: "& First
Debug.Print ndent &" id: "& snd
Debug.Print ndent &"description: "& Third
Debug.Print Emails
Debug.Print ndent &" name: "& Fourth
Debug.Print ndent &" archived: "& Fifth
Debug.Print
Debug.Print "End of array in ROOT - fixVersions"
Debug.Print FirstCommentID
Debug.Print LastCommentID
Debug.Print Emails
Debug.Print LastField
EndSub