#1
|
|||
|
|||
Get Zip code detail via JSON in VBA.
Hello All,
I need to call JSON API in VBA which will set JSON values into the variables. Requirement - I have zip codes which will pass with JSON API and receive Latitude and Longitude. JSON API - https://api.zip-codes.com/ZipCodesAP...odeDetails/A0A 1E0?key=DEMOKEYVAL Can anyone suggest me on call JSON API in VBA? Many Thanks! |
#2
|
|||
|
|||
Install the parsing routine as I commented. Add the library as commented since the parsing routine needs it. Pass your own key value.
Code:
Sub Test_QuickGetZipCodeDetails() 'Register to get key: https://www.zip-codes.com/zip-code-api-register.asp '250 free queries per month limit. MsgBox Join(QuickGetZipCodeDetails(90210, "123456789abcdefghij"), vbLf) End Sub 'Get latest jsonconverter.bas, https://github.com/VBA-tools/VBA-JSON/releases 'The routine above needs library: Tools > References > Microsoft Scripting Runtime 'Similar to: https://codingislove.com/excel-json/ Function QuickGetZipCodeDetails(zip, key$) Dim http As Object, JSON As Object, a(1 To 6), s$ Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", "https://api.zip-codes.com/ZipCodesAPI.svc/1.0/QuickGetZipCodeDetails/" & _ zip & "?key=" & key, False http.Send s = http.responseText Set JSON = ParseJson(s) a(1) = JSON("City") a(2) = JSON("State") a(3) = JSON("Latitude") a(4) = JSON("Longitude") a(5) = JSON("ZipCode") a(6) = JSON("County") QuickGetZipCodeDetails = a End Function |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Comparing detail from two work books | faisalm2 | Excel | 8 | 06-07-2017 01:00 AM |
How to Work with JSON in word-vba | PRA007 | Word VBA | 1 | 05-01-2016 02:53 PM |
How to Improve task Detail in Team Planner ? | Tray | Project | 9 | 07-07-2013 03:44 PM |
Accessing Assignment Detail Data in Task Usage View | CGM3 | Project | 0 | 10-05-2010 05:56 AM |