We help IT Professionals succeed at work.

EXCEL VBA Code to get API Data and output to excel table

12,889 Views
Last Modified: 2017-05-17
I would like to get API data in excel using VBA code and output the results to an excel sheet but am unsure how to do this as I am new to getting API data into Excel. I have tried to use the Data From Web option but cannot get it to recognise the API Link.

If possible I also want to be able to query the url's from the first sheet and output the results to another sheet.

First Sheet looks like this:

Sheet 1
The first API Link is:  https://api.goformz.com/v2/templates/6c6fc77d-6902-4278-b96e-a471004a331c/formz?sort=name&name=&status=complete&pagesize=500

The url' s i need to query from the first sheet are in column "B" and I need them to output to a second sheet called sheet 2

I am told that i can use a JSON module to customise the output / get the data but again unsure where to start.

I have also attached a copy of the Excel Sheet and XML data
Results.xlsx
data.xml
Comment
Watch Question

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
what about the login credentials?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Troy GrahamIT Manager

Author

Commented:
I have setup the following VBA code in my excel sheet which gets the data i need from the website and saves it to a JSON file. How do I then get the JSON file contents into an excel sheet ?

Here is the code I am using below and I have attached a copy of the JSON file contents (in a text file)

Sub get_data(up_http, down_http)
Dim xmlhttp: Set xmlhttp = CreateObject("msxml2.xmlhttp.6.0")
xmlhttp.Open "get", up_http, False
xmlhttp.send
Dim fso: Set fso = CreateObject("scripting.filesystemobject")
Dim newfile: Set newfile = fso.createtextfile(down_http, True)
newfile.write (xmlhttp.responseText)
newfile.Close
Set newfile = Nothing
Set xmlhttp = Nothing
End Sub

Sub Button1_Click()
get_data "https://api.goformz.com/v2/formz/a047e224-b903-4698-aa22-a745004737a0", "C:\Users\troy\Documents\Goformz.json"
End Sub

Open in new window

Goformz.txt
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Unfortunately getting a JSON dataset into Excel (or even CSV) is not easy, so can't really give you a solution for that.  A few thoughts though...

Have you tried pulling down the data in XML format rather than JSON, Excel can open XML files, and since this one isn't too complex it might not work out too bad.  It looked like the API you are getting the data from supported XML format as well as JSON.

Here is a related question I participated in related to REST and JSON from Excel.  You might want to take a look at it, and some of the linked items in case any of it is useful to you.

https://www.experts-exchange.com//questions/28997478/Sunrise-Sunset-Excel-2007-Formula.html

In Javascript this gets a lot easier, what would you think of using a Windows command line WSH script that read the JSON file you saved and created a CSV file from it?  Then you could open that in Excel.

~bp
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
How do I then get the JSON file contents into an excel sheet ?

As noted, my link contains full code for a proven solution and a demo/test which is easy to modify.

/gustav
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.