Parse JSON/XML parameters from web API

Question

This is a quick and dirty POC I have so far from other helpful Stack posts:

Public Function WebRequest(url As String) As String
    Dim http As MSXML2.xmlhttp
    Set http = CreateObject("MSXML2.ServerXMLHTTP")

    http.open "GET", url, False
    http.send

    WebRequest = http.responseText
    Set http = Nothing
End Function

Private Sub Command1_Click()

Dim http As MSXML2.xmlhttp
    Dim result As String
    Dim url As String
    Dim productId As String

    productId = "2"
    url = "http://localhost:1111/api/products/" & productId
    result = WebRequest(url)

    MsgBox result

End Sub

This calls a simple web API and returns as expected. The response reads as:

{"Id":2,"Name":"Yo-yo","Category":"Toys","Price":3.75}

What is the best way to assign the parameters to variables for use within the rest of the app?

@Bob77 I very quickly copied this answer: https://stackoverflow.com/a/27854657/7896203 I've never worked with VB6 before so I can't answer that question to be honest. — Reed, Jul 17 '17 at 13:29
Possible duplicate of [Is There a JSON Parser for VB6 / VBA?](https://stackoverflow.com/questions/2782076/is-there-a-json-parser-for-vb6-vba) — StayOnTarget, Sep 12 '19 at 11:22

Bob77 · Accepted Answer · 2017-07-16T16:53:05.590

There is no "best" way to parse JSON, but there are several existing VB6 classes for doing so. There is nothing built into VB6 or in Windows you can use though, so there isn't any obvious choice to reach for first.

If you don't want to use an existing VB6 class or a 3rd party library then you could just "manually" do the parsing with your own code. As long as the JSON you expect is pretty simple that might be all you need.

Many pitfalls here but it works for your very simple case as long as no other data types are used, the strings never have quotes or escaped symbols, etc.:

Option Explicit

Private Sub Main()
    Const SIMPLE_JSON As String = _
        "{""Id"":2,""Name"":""Yo-yo"",""Category"":""Toys"",""Price"":3.75}"
    Dim JsonItems() As String
    Dim Collection As Collection
    Dim I As Long
    Dim Parts() As String
    Dim Value As Variant

    JsonItems = Split(Mid$(SIMPLE_JSON, 2, Len(SIMPLE_JSON) - 2), ",")
    Set Collection = New Collection
    For I = 0 To UBound(JsonItems)
        Parts = Split(JsonItems(I), ":")
        Parts(0) = Mid$(Parts(0), 2, Len(Parts(0)) - 2)
        If Left$(Parts(1), 1) = """" Then
            Value = Mid$(Parts(1), 2, Len(Parts(1)) - 2)
        Else
            Value = Val(Parts(1))
        End If
        Collection.Add Array(Parts(0), Value), Parts(0)
    Next

    With Collection
        For I = 1 To .Count
            Debug.Print .Item(I)(0); "="; .Item(I)(1)
        Next
    End With
End Sub

Result:

Id= 2 
Name=Yo-yo
Category=Toys
Price= 3.75

The Val() function is used for the non-String values because it is locale blind (always uses the invariant locale, which JSON numbers should always be formatted for).

Thanks for the help! I assumed it wouldn't be very straight forward but this is my best resource going forward thus far! I'll have to further see what data types are being used in the legacy service I am retrofitting. Thanks! — Reed, Jul 17 '17 at 13:31

Parse JSON/XML parameters from web API

1 Answers1