EXCEL VBA Code to get API Data and output to excel table
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:
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
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:
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
aikimark
what about the login credentials?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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
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
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
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