parse JSON array in excel vba

Question

I have done a post URL and I get JSON format respondtext. I want to get "barcodes" starting from cell(1,1) This is what JSON response is:

"{
    ""status"": ""success"",
    ""message"": ""5 New Barcode(s) Generated for batch 5924592"",
    ""data"": {
        ""batch"": ""5924592"",
        ""barcodes"": [
            ""MN8HY6"",
            ""5BZZ9K"",
            ""9R6QKR"",
            ""869P8Z"",
            ""XK2UXZ""
        ]
    }
}"

Here is what I have written so far on VBA excel

Dim Json As Object
Dim xmlhttp As New MSXML2.XMLHTTP60
Dim Parsed As Dictionary 
Dim Item As Dictionary    

'Post
xmlhttp.Open "POST", "url", False
'setting auth headers
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xmlhttp.setRequestHeader "Authorization", "token"

Dim Bqty As Variant
Dim bID As Variant

Bqty = Sheets("sheet1").Range("f1").Value
bID = Sheets("sheet1").Range("f4").Value

xmlhttp.send "action=" & "createBarcodes" & _
Chr(38) & "barcodes=" & Bqty & _
Chr(38) & "batch=" & bID


JsonString = xmlhttp.responseText

Set Json = JsonConverter.ParseJson(xmlhttp.responseText)
Set Parsed = JsonConverter.ParseJson(xmlhttp.responseText)

 Dim i As Integer
 i = 1
 For Each Item In Parsed("data")
 Sheets("Batch ID").Cells(i, 1).Value = Item("barcodes")
 i = i + 1
 Next

Please if someone can help as I have spent all day figuring this out. I am not a professional VBA developer.

Thank you

i get an error, "OBJECT REQUIRED" when i run my script :(

Tarinder
– Tarinder

2018-03-18 02:32:51 +00:00
Commented Mar 18, 2018 at 2:32 — Tarinder
– Tarinder, Commented Mar 18, 2018 at 2:32

user4039065 · Accepted Answer · 2018-03-18 03:55:16Z

1

Do you need a JSON library?

Dim i As Long, strresp As String, bcodes As Variant

strresp = xmlhttp.responseText

strresp = Mid(strresp, InStr(1, strresp, Chr(91) & Chr(32) & Chr(34) & Chr(34)) + 4)
strresp = Left(strresp, InStrRev(strresp, Chr(34) & Chr(34) & Chr(32) & Chr(93)) - 1)

bcodes = Split(strresp, Chr(34) & Chr(34) & Chr(44) & Chr(32) & Chr(34) & Chr(34))

For i = LBound(bcodes) To UBound(bcodes)
    Debug.Print bcodes(i)
Next i

Worksheets("Batch ID").Cells(1, 1).Resize(UBound(bcodes) + 1, 1) = _
    Application.Transpose(bcodes)

Granted all the Chr()'s make it look messy but I hate doubling up quotes within a quoted string.

answered Mar 18, 2018 at 3:55

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

K.Dᴀᴠɪs Over a year ago

I agree on the double quotes. My preference would to use a const in that scenario: Const dq = Chr(34) & Chr(34). Probably not the best programming tactic, but I am not known for the best practices sometimes

user4039065 Over a year ago

This was just a demo and its usefulness is going to depend on what the responseText actually looks like. Probably better to Mid the string between [ and ] then regex out the barcodes from there.

Collectives™ on Stack Overflow

parse JSON array in excel vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related