What can you learn here?
- Using cDataSet
- Using cRest
- Using cJobject

Putting the Rest response on the sheets get it now
Now that we have the tools to generate existing or adhoc rest queries, let’s look at how the data gets into the given excel Sheet.
Try it out first ?
These are enhancements to Data Manipulation Classes so they have been included in cDataSet.xlsm, which is downloadable from Download Complete Projects.
Use DataSets
As in most of the projects on this site we use Data Manipulation Classes to abstract the data from the Excel structure, and How to use cJobject will come in handy to understand the concept of dealing with jSon. All of these, including the Rest to Excel library are implemented inside the cDataset.xlsm downloadable workbook, along with examples of their use.
Walkthrough of populating sheet and executing rest queries.
Here is the code executed by the restQuery procedure to deal set up the abstraction and execute the query
' lets get the data Set dSet = New cDataSet With dSet.populateData(wholeSheet(sName), , sName, , , , True) ' ensure that the query column exists if it was asked for If qType = erQueryPerRow Then If Not .HeadingRow.Validate(True, sQueryColumn) Then Exit Function End If ' alsmost there Set cr = New cRest Set cr = cr.init(sResults, qType, .HeadingRow.Exists(sQueryColumn), _ , dSet, bPopulate, sUrl, bClearMissing, bTreeSearch, complain) If cr Is Nothing Then If complain Then MsgBox ("failed to initialize a rest class") Else Set cr = cr.execute(sQuery) If cr Is Nothing Then If complain Then MsgBox ("failed to execute " & sQuery) Else Set restQuery = cr End If End If End With
Create a new dataset based on the sheetName
With dSet.populateData(wholeSheet(sName), , sName, , , , True)
Make sure that the the given queryColumn exists
If qType = erQueryPerRow Then If Not .HeadingRow.Validate(True, sQueryColumn) Then Exit Function End If
Use the cRest class to setup a query, or a series of queries based on the parameters from the rest library, and given as arguments
Set cr = New cRest Set cr = cr.init(sResults, qType, .HeadingRow.Exists(sQueryColumn), _ , dSet, bPopulate, sUrl, bClearMissing, bTreeSearch, complain)
Execute that query against the dataSet, and fill it up
If cr Is Nothing Then If complain Then MsgBox ("failed to initialize a rest class") Else Set cr = cr.execute(sQuery) If cr Is Nothing Then If complain Then MsgBox ("failed to execute " & sQuery) Else Set restQuery = cr End If End If
Summary
The use of Data Manipulation Classes eliminates that messy business of working with Excel Ranges and so on, and since the column headings are set up to match the required fields in the rest query response, there is not much to do here. The real work happens in the cRest class.
In the meantime why not join our forum to ensure you get updates when they are available.
You can also submit anything you want considered for publication on this site (for example if you add interesting Rest calls to the restLibrary) to our forum. Now let’s look at Advanced rest library usage