Setting up the workbook layout and format

Here we are going to create a workbook from the JSON manifest. The relevant section is below, and can be found at manifest.child(“setup”). Each type of data is identifed by { “type”: “someType” } and contains options {} for how to handle the API response, some formatting information. In addition, APIs often return time in UNIX format (see this post for how to deal with those in Excel), so convertTimes{} describes which columns need to be converted from Unix times, and where to put the resultant Excel Time.

Here’s the code


Deleting existing Sheets

All the sheets to contain data returned from the bitCoin API will be called something like type_venue, so the first step is to delete any existing sheets for each type in the manifest

Creating the new sheets

Formatting the heading cells

We can use the functions described in Playing around with colors in VBA to apply the hex color provided in the options to the headers, as well as to colorize the font to a suitable contrasting color.

For more on this see. Data driven VBA apps with JSON