使用VBA解析JSON(Access 2010) [英] Parse JSON with VBA (Access 2010)

问题描述

我需要使用以下JSON文件更新MS-Access中的货币表:

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
}
}

VBA代码如下:

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

上面的代码可以正常显示带有CAD的消息框，但是我需要遍历并捕获每个货币代码及其汇率值.我使用什么语法来做到这一点?我可以为函数Json()函数提供代码，但没有找到上载它的方法.任何帮助将不胜感激.

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.

推荐答案

如果您正在使用此json解析器 https://github.com/VBA-tools/VBA-JSON ，请使用此代码

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

您必须使用任何您想做的过程来修改debug.print.要在您的代码中使用此代码，请将此行放在MsgBox之后.

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

这篇关于使用VBA解析JSON(Access 2010)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！