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