Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-18-2018, 03:08 AM
Mangesh1212 Mangesh1212 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Nov 2018
Posts: 8
Mangesh1212 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 12-18-2018, 07:26 PM
Kenneth Hobson Kenneth Hobson is offline Windows 10 Office 2016
Novice
 
Join Date: Jun 2018
Posts: 27
Kenneth Hobson is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 03:42 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft