如何将fixer.io json输出解析为excel(用于虚拟人) [英] How to parse fixer.io json output into excel (for dummies)

问题描述

我在VBA中使用FIXER.IO流行的API将汇率输入到工作表中名为USD，CNY，INR等的某些单元格中. Fixer.io API返回的文本格式给出了我需要的费率. 自2018年3月6日起，旧版Fixer API(api.fixer.io)已弃用，并更改为需要API访问密钥(在注册时获取)的新版本，但仅返回JSON文件.如果我拨打网址:

I use FIXER.IO popular API in a VBA to get exchange rates into some cells of my worksheet named USD, CNY, INR etc. Fixer.io API was returning a text format giving the rates I needed. As of March 6th 2018, the legacy Fixer API (api.fixer.io) was deprecated and changed into a new version that requires an API Access Key (got it upon registration) but only returns a JSON file. If I call the url:

http://data.fixer.io/api/latest?access_key=XXXXXXXXXXXX&symbols=USD,CNY,INR,THB,SGD,AUD

我得到此JSON作为回报:

I get this JSON in return:

{"success":true,"timestamp":1523343843,"base":"EUR","date":"2018-04-10","rates":{"USD":1.231986,"CNY":7.757563,"INR":79.980529,"THB":38.462602,"SGD":1.614924,"AUD":1.592345}}

如何解析Excel变量(USD，CNY ...)中的交换值? 我试图环顾四周，但是我非常有限的编程技能没有帮助我适应任何解决方案.请给假人"回复:) 感谢您提供的任何帮助 问候 马可

How can I parse the exchange values in my Excel variables (USD, CNY ...) ? I tried to look around but my very limited programming skill did not help me to adapt any solution. Please give a "for dummy" reply :) Thanks for any help provided Regards Marco

推荐答案

看看下面的示例. 将 JSON.bas 模块导入VBA项目中以进行JSON处理.

Take a look at the below example. Import JSON.bas module into the VBA project for JSON processing.

Option Explicit

Sub Test()

    Dim sJSONString As String
    Dim vJSON
    Dim sState As String
    Dim aData()
    Dim aHeader()

    ' Retrieve data
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://data.fixer.io/api/latest?access_key=209f86f5304e0043a0879d8cb45c9c10&symbols=USD,CNY,INR,THB,SGD,AUD", False
        .Send
        sJSONString = .ResponseText
    End With
    ' Parse JSON response
    JSON.Parse sJSONString, vJSON, sState
    ' Refer to target dictionary containing rates
    Set vJSON = vJSON("rates")
    ' Access to each item in dictionary
    Debug.Print vJSON("USD")
    Debug.Print vJSON("CNY")
    Debug.Print vJSON("INR")
    Debug.Print vJSON("THB")
    Debug.Print vJSON("SGD")
    Debug.Print vJSON("AUD")
    ' Convert to array and output to worksheet
    JSON.ToArray vJSON, aData, aHeader
    With Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

对我来说，输出如下:

顺便说一句，在以下答案中应用了类似的方法: 1 ， 3 ，4 ， 5 ， 7 ，8 ， 9 ， 10 ， 11 ， 12 ， 13 ， 14 ， 15 d 16 .

BTW, the similar approach applied in the following answers: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 and 16.

这篇关于如何将fixer.io json输出解析为excel(用于虚拟人)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！