Parse JSON with VBA (Access 2010)

Question

I need to update a currency table in MS-Access with a JSON file below:

{
"timestamp": 1465843806,
"base": "CAD",
"rates": {
"AED": 2.87198141,
"AFN": 54.21812828,
"ALL": 95.86530071,
"AMD": 374.48549935,
"ANG": 1.39861507
}
}

The VBA code is as follows:

Private Sub cmdJsonTest_Click()
Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
MyRequest.Open "GET", "https://website.org/api/latest.json?base=CAD"
MyRequest.send
' MsgBox MyRequest.ResponseText
Dim Json As Object
Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)
MsgBox Json("base")  
End Sub

The above code works correctly displaying a message box with CAD but I need to loop through and capture each currency code along with it's rate value. What syntax do I use to do this? I can provide the code for the function Json() function but did not see a way to upload it. Any assistance would be appreciated.

Jules · Accepted Answer · 2016-06-16 21:49:28Z

3

If you are using this json parser https://github.com/VBA-tools/VBA-JSON, use this code

Private Sub IterateDictionary(poDict As Dictionary)
    Dim key As Variant

    For Each key In poDict.Keys()
        If TypeName(poDict(key)) = "Dictionary" Then
            Debug.Print key
            IterateDictionary poDict(key)
        Else
            Debug.Print key, poDict(key)
        End If

    Next
End Sub

EDIT: You have to modify the debug.print with whatever process you want to do. To use this from your code put this line after MsgBox.

IterateDictionary Json

edited Jun 16, 2016 at 21:49

answered Jun 16, 2016 at 5:59

Jules

1,42313 silver badges22 bronze badges

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

3 Comments

John C Over a year ago

Yes this is the json parser that I am using. Sorry if this is a stupid question but could you please outline how I would call your function from my function?

John C Over a year ago

I see the results in the Immediate Window when I run the code and it is working. This is exactly what I needed to do - thank you! Now I just have to figure out the best way to update the currency table. I'll have to create a temporary recordset and the either loop through each matching currency code and update or execute an update query. Thanks again. How do I mark your solution as the answer?

Jules Over a year ago

@JohnC Glad to help, there should be a green check mark.

QHarr · Accepted Answer · 2018-07-05 06:14:18Z

2

You could also string parse. For example, if after key pairs for the rates:

Option Explicit
Public Sub GetValues()
    Dim s As String, rates(), i As Long
    s = "{""timestamp"": 1465843806,""base"": ""CAD"",""rates"": {""AED"": 2.87198141,""AFN"": 54.21812828,""ALL"": 95.86530071,""AMD"": 374.48549935,""ANG"": 1.39861507}}"
    rates = Array("AED", "AFN", "ALL", "AMD", "ANG")

    For i = LBound(rates) To UBound(rates)
        Debug.Print rates(i) & ":" & GetRate(s, rates(i))
    Next i
End Sub
Public Function GetRate(ByVal s As String, ByVal delimiter As String) As String
    GetRate = Replace(Split(Split(s, delimiter & Chr$(34) & Chr$(58))(1), Chr$(44))(0), Chr$(125), vbNullString)
End Function

answered Jul 5, 2018 at 6:14

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Collectives™ on Stack Overflow

Parse JSON with VBA (Access 2010)

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related