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
<pre>' 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</pre>
Create a new dataset based on the sheetName
<pre>With dSet.populateData(wholeSheet(sName), , sName, , , , True)</pre>
Make sure that the the given queryColumn exists
<pre>If qType = erQueryPerRow Then
If Not .HeadingRow.Validate(True, sQueryColumn) Then Exit Function
End If</pre>
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
<pre>Set cr = New cRest
Set cr = cr.init(sResults, qType, .HeadingRow.Exists(sQueryColumn), _
, dSet, bPopulate, sUrl, bClearMissing, bTreeSearch, complain)</pre>
Execute that query against the dataSet, and fill it up
<pre> 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</pre>
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