+ Reply to Thread
Results 1 to 2 of 2

Use Excel VBA to Get and parse an API JSON response

  1. #1
    Registered User
    Join Date
    12-12-2013
    Location
    Northern VA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Use Excel VBA to Get and parse an API JSON response

    I have a csv file containing UPC codes. I'd like to use 'http://www.upcitemdb.com/api/explorer#!/lookup/get_trial_lookup' to get product info on these UPCs. the response url 'https://api.upcitemdb.com/prod/trial/lookup?upc=' returns a JSON response.

    I'm trying to very quickly learn python but i'm having trouble installing some of the libraries that are required.

    Is there a way to parse and save the response for each upc in the csv using excel VBA?

    here is the response for https://api.upcitemdb.com/prod/trial...=0032076898106

    {"code":"OK","total":1,"offset":0,"items":[{"ean":"0032076898106","title":"Gardner Bender GHS-1510 3/4-Inch by 10-Foot Galvanized Hanger Strap","description":"<li>Hanger strap <","upc":"032076898106","brand":"Gardner Bender","model":"0003207689810","color":"Gold","size":"","dimension":"3 X 3 X 2 inches","weight":"0.7 Pounds","currency":"","lowest_recorded_price":0.01,"images":["http://images10.newegg.com/ProductImageCompressAll200/A549_1_2014102462285296.jpg","http://i.walmartimages.com/i/mp/00/03/20/76/89/0003207689810_P255045_500X500.jpg","https://i5.walmartimages.com/asr/acaf0203-347b-4b78-be21-72842d32c4ba_1.30170b89f03dd9fe212a9db82e251470.jpeg?odnHeight=450&odnWidth=450&odnBg=ffffff","http://img1.r10.io/PIC/1320524/0/1/250/1320524.jpg","http://c.shld.net/rpx/i/s/pi/mp/4143/7714716818?src=http%3A%2F%2Ficdn.tonzof.com%2F8%2F4%2F9%2F849032076898106%2FGardner_Bender_GHS-1510_34-in_X_10_Galvanized_Hanger_Strap_1.jpg&d=692cb8d20049aae99dcfd375edc393a50b544ed4","http://site.unbeatablesale.com/img241/jnsn2316.gif"],"offers":[{"merchant":"UnbeatableSale.com","domain":"unbeatablesale.com","title":"Gardner Bender .75in. X 10ft. Galvanized Hanger Strap GHS-1510","currency":"","list_price":"","price":11.45,"shipping":"6.64","condition":"New","availability":"","link":"http://www.upcitemdb.com/norob/alink/?id=v2o26323w24364d4&tid=1&seq=1484160199&plt=62e5106c300f76c2c041634c4b3a5ef0","updated_t":1484154951},{"merchant":"Sears","domain":"sears.com","title":"Gardner Bender GHS-1510 3/4-in X 10 Galvanized Hanger Strap","currency":"","list_price":"","price":19.65,"shipping":"","condition":"New","availability":"","link":"http://www.upcitemdb.com/norob/alink/?id=23o2632313y2c444&tid=1&seq=1484160199&plt=17730278c924edc0a3a6c8e68c97b8c9","updated_t":1425698758},{"merchant":"Rakuten(Buy.com)","domain":"rakuten.com","title":"Gardner Bender .75in. X 10ft. Galvanized Hanger Strap GHS-1510","currency":"","list_price":"","price":11.45,"shipping":"6.78","condition":"New","availability":"","link":"http://www.upcitemdb.com/norob/alink/?id=w2v263z2v213c474&tid=1&seq=1484160199&plt=602a951329cf252a992588fb3ebe92bc","updated_t":1482963456},{"merchant":"Wal-Mart.com","domain":"walmart.com","title":"GB Gardner Bender GHS-1510 3/4\" x 10' Galvanized Hanger Strap","currency":"","list_price":"","price":9.5,"shipping":"6.57","condition":"New","availability":"","link":"http://www.upcitemdb.com/norob/alink/?id=u2u23313z2637484s2&tid=1&seq=1484160199&plt=d615f74b956a3541ae9f5644fb7bad3a","updated_t":1483234361},{"merchant":"Walmart Marketplace","domain":"walmart.com","title":"Gardner Bender GHS-1510 3/4-in X 10 Galvanized Hanger Strap","currency":"","list_price":"","price":14.99,"shipping":"4.99","condition":"New","availability":"","link":"http://www.upcitemdb.com/norob/alink/?id=u2o233z233x284d4y2&tid=1&seq=1484160199&plt=bf928dc6d2c7acf2f04677898fe50e8e","updated_t":1426624585},{"merchant":"Newegg Business","domain":"neweggbusiness.com","title":"GB Gardner Bender Hangr Strap 3/4\"X10' 1040-9530","currency":"","list_price":"","price":36.32,"shipping":"5.99","condition":"New","availability":"","link":"http://www.upcitemdb.com/norob/alink/?id=v2o2632323339454w2&tid=1&seq=1484160199&plt=6378cc7418e175b384d696d1c8cf709d","updated_t":1484148425}],"asin":"B0012ZKY4O","elid":"391598699249"}]}

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Use Excel VBA to Get and parse an API JSON response

    you can check https://github.com/VBA-tools/VBA-JSON
    however I would suggest to stick to your initial intent - python. it would be much easier. check https://python-forum.io Many people would be happy to assist with installation problems you have
    Last edited by buran; 01-11-2017 at 05:28 PM.
    If you are pleased with a member's answer then use the Star icon to rate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Parse / flatten nested JSON string in VBA
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-23-2016, 08:45 AM
  2. Parsing JSON to excel
    By ErgoTeam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2016, 02:54 AM
  3. Excel VBA-JSON Example
    By manofcheese in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2016, 11:52 AM
  4. Json to Excel Conversion
    By shrikant_chandurkar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-19-2015, 03:50 AM
  5. API Json to Excel Macro
    By ermengard in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2014, 05:33 AM
  6. Json file from URL to Excel
    By KK33317 in forum Tips and Tutorials
    Replies: 0
    Last Post: 03-13-2014, 07:39 AM
  7. Parse xml response
    By Chaitanyakayasri in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2013, 08:23 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1