Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

LinkBack Thread Tools Display Modes
Old 12-18-2018, 03:08 AM
Mangesh1212 Mangesh1212 is offline Windows 7 32bit Office 2007
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.

Can anyone suggest me on call JSON API in VBA?

Many Thanks!
Reply With Quote
Old 12-18-2018, 07:26 PM
Kenneth Hobson Kenneth Hobson is offline Windows 10 Office 2016
Join Date: Jun 2018
Posts: 27
Kenneth Hobson is on a distinguished road

Install the parsing routine as I commented. Add the library as commented since the parsing routine needs it. Pass your own key value.

Sub Test_QuickGetZipCodeDetails()
  'Register to get key:
  '250 free queries per month limit.
  MsgBox Join(QuickGetZipCodeDetails(90210, "123456789abcdefghij"), vbLf)
End Sub

'Get latest jsonconverter.bas,
'The routine above needs library: Tools > References > Microsoft Scripting Runtime
'Similar to:
Function QuickGetZipCodeDetails(zip, key$)
  Dim http As Object, JSON As Object, a(1 To 6), s$
  Set http = CreateObject("MSXML2.XMLHTTP")
  http.Open "GET", "" & _
    zip & "?key=" & key, False
  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

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. is not affiliated with Microsoft