We help IT Professionals succeed at work.

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

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
Watch Question

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

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


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
Dim fso: Set fso = CreateObject("scripting.filesystemobject")
Dim newfile: Set newfile = fso.createtextfile(down_http, True)
newfile.write (xmlhttp.responseText)
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

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

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.


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.

Most Valuable Expert 2015
Distinguished Expert 2018

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.

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.


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.