JSON to Table Format Using VBA (data from GSA's per diem API)

[osinrider04]

Hey everyone - I hope that this thread can be found useful in the future for others once the answer is figured out. Thanks to the push for open data within the Federal Government, you can now go to many Agency websites and connect to their data through their website to run queries and return certain sets of data. However, in my situation, I typically want the entire data set. There has been a trend lately of these Agencies that allow you to tap into their data with their API's. However, these API's are also commonly stored in .JSON files. The question for the Excel gurus out there is to help walk me through an example of turning a .JSON file into an excel table using VBA. I know there are threads already out there on this but none of them were easy enough for me to follow. I do not have any background in .JSON which is primarily where my struggle comes in.

The example I would like to use is GSA's per diem data found here: https://catalog.data.gov/dataset/per-diem-api
The data when downloaded is at this URL: https://inventory.data.gov/api/acti...ource_id=8ea44bc4-22ba-4386-b84c-1494ab28964b

The question: Can someone out there help me with some VBA that allows for a repeatable approach to convert an API link to a data table in Excel? Generally all of the workbooks I create with macros are performed through clickable buttons as the "UI" (in case it helps our development of a solution). I understand that GSA technically has a file you can download as a .csv, but this is for all of the other places that use API's in .JSON formats.

Thanks in advance!