Automatically get latest currency exchange rates into Access 97

How about this webservice, it returns results in JSON, but that wouldn't be hard to parse and load into Access table.

• Foreign exchange rates and currency conversion JSON API

---

»bp

I saw that, but I have no idea how to use it or parse it into a simple number like "1.2355" for USD to CAD.
Any help would be much appreciated!!!

OK, using: https://api.fixer.io/latest?base=USD

I get this saved as an *.ini file on my local drive:

{"base":"USD","date":"2017-11-07","rates":{"AUD":1.307,"BGN":1.6916,"BRL":3.2712,"CAD":1.2767,"CHF":0.99957,"CNY":6.6381,"CZK":22.134,"DKK":6.4369,"GBP":0.76144,"HKD":7.8032,"HRK":6.5227,"HUF":268.98,"IDR":13528.0,"ILS":3.5144,"INR":64.995,"JPY":114.19,"KRW":1111.6,"MXN":19.089,"MYR":4.2231,"NOK":8.177,"NZD":1.4459,"PHP":51.398,"PLN":3.6668,"RON":3.9738,"RUB":58.851,"SEK":8.4279,"SGD":1.3641,"THB":33.13,"TRY":3.8638,"ZAR":14.172,"EUR":0.8649}}    

Now, how can extract the "CAD":1.2767 so I can use that exchange rate?

From all these GREAT ideas...I got it to work!!!
After saving the JSON file locally, I open it and search for "CAD" using Mid+InStr:

    Open CompanyProfile("ClientDatabaseFolderPath") & "\ExchangeRate.ini" For Input As #1    'Open file for input.
    Input #1, GetExchangeRate  
    Close #1    ' Close file.
    GetExchangeRate = Mid(strTextLine, InStr(strTextLine, "TRY") + 5, 6)

Select allOpen in new window

Thanks everyone!!!

Oops my code should have been:
GetExchangeRate = Mid(strTextLine, InStr(strTextLine, "CAD") + 5, 6)

Great! Though you shouldn't have to save the Json first as a file.

/gustav

Yes, good point Gustav!
I tried your code, but it does not work in Access 97.
There is no function "RetrieveDataResponse()" in Access 97.

Sorry Gustav! I did not import the VBA into Access yet....my bad!
Will try again!

Well, for a single tiny file, it won't make much of a difference ...

/gustav