Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    Nope, no joy, but thanks...

    working on it..

  2. #17
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    Fixed just a fyi

    url = "https://api/v3/parts/match?apikey=abb074e0&queries="
    url2 = "[{" & """mpn""" & ":" & """SN74S74N""" & "}]"

    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    MyRequest.Open "GET", url & url2
    'MyRequest.Open "GET", "https://jira.atlassian.com/rest/api/2/issue/JRA-9.json"
    'MyRequest.Send
    MsgBox url & url2

    Why so hard? must be an easier way

  3. #18
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #19
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    I finally got it to work (on my real file) it wasn't easy at all (to me anyway) but now I have to figure out the complex structure of the JSON, which is proving to be harder yet.

    Those nested arrays are a pain in the ...., time to read read read, coffee coffee coffee, type type type and start the whole thing over. I am truly amazed there is not more documentation this procedure considering the intense implementation of JSON.

    Thanks again for your help, I haven't even begun to write the real code to actually populate the table with the data, but first I have to figure out how to read the JSON.

  5. #20
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    wrayman,

    I'd like to see what you have done regarding
    I finally got it to work (on my real file)
    .

    There are so few working examples of parsing json with vba that any info would be appreciated.
    Glad to hear you are making progress.

  6. #21
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46

    So far I got this point

    #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
    Last edited by wrayman; 11-01-2016 at 05:58 PM. Reason: Left Out Access

  7. #22
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just got back to this??

    Does not use Option Explicit so many variables are not defined.
    Also, there are issues (could be copy and paste related) where words are concatenated causing VBA syntax errors.

    After trying to work with the code, I did get some response text returned, but it continually had issues and failed.(I got frustrated and gave up).
    Still looking for a working example of a JSON parser in vba with documentation and preferably a working example.

  8. #23
    ivonsurf123 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2018
    Posts
    1

    Exporting/writing JSON to populate Fields

    Hello,

    I am new to the concept of Rest API and JSON, do you know how would you Export/Writing JSON to populate Fields on an access form?

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. System DSN (MS Access) to REST API / JSON
    By Hitesh in forum Queries
    Replies: 1
    Last Post: 03-15-2016, 11:37 AM
  2. Reading fields in querydef - how to find criteria
    By aytee111 in forum Programming
    Replies: 4
    Last Post: 05-08-2014, 03:44 PM
  3. Replies: 3
    Last Post: 05-03-2014, 11:00 AM
  4. Replies: 8
    Last Post: 08-02-2012, 10:50 AM
  5. Replies: 1
    Last Post: 06-04-2012, 12:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums