What can you learn here?

  • Using cDataSet
  • Using cRest
  • Using cJobject
What can you learn here

Public versus Private
Variable visibility
External references

Doing unusual things with the rest library get it now


Having dealt with Rest to Excel libraryAdding to the rest libraryAdhoc Rest Queries and Populating sheets with Rest responses let’s take a look at how do modify the usual behavior of the cRest class

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.


restQuery arguments


restQuery is the main interface into the restLibrary module, although simpler versions with less arguments for completely standard behavior are also available, as per Rest to Excel library
Here are the arguments for all functions in restLibrary


Arguments for restQuery

 Argument  Purpose
 sName The worksheet Name that will be populated (and will provide the input data if needed as for an erQueryPerRow type)
 sEntry  The given name to this query in the restLibrary. If not supplied, then sRestUrl should contain the URL of an adhoc rest query.
 sQuery  The query string to use for an erSingleQuery type.
 sQueryColumn  The name of the column that contains the query data for an erQueryPerRow type.
 sRestUrl  The Url of an adhoc rest query. Not needed if you supply sEntry to specify an already known library query
 sResponseResults  The container name for the results returned from the query. Not needed if you supply sEntry to specify an already known library query
 bTreeSearch  Whether to search the tree beyond the first level of sResponseResults to try to match column names. Not needed if you supply sEntry to specify an already known library query.
 bPopulate  Whether or not to actually populate the worksheet. 
 bClearMissing  This will clear cells that are missing from a response in an erQueryPerRow type.
 complain  This says whether to complain about errors that are found or to deal with them silently.
 queryCanBeBlank  It’s ok for the  sQuery argument to be blank
 sFix  This is a value containing a jSon string. You’d use if you didnt actually want to make an API call, but already had the string, say in a spreadsheet cell
 user  restQuery can do basic http authentication – this is the user name if you are using it
 pass password for basic http authentication    
 append  whether or not to append data to the existing sheet
 stampQuery  You may want to stamp each row with the query used. This is the name of the column to stamp. Often used in conjunction with appending data
 appendQuery  You may have an additional query to add to the end of a url. Specify it here
 collectionNeeded  By default, cRest will maintain a collection of each cJobject processed in case you need to do further processing. If you dont need it, then setting this to false can use less memory.

Why execute a query without populating the sheet.


In addition to populating a sheet, the cRest.execute method also maintains the cRest.jObjects property, which is a collection of cJobjects representing the response to every query that was executed. This means that you can use this class to do more than just filling sheets. You may want to manipulate the responsese before filling sheets or use them in some other way. Here is an example where no sheets are populated, and the jOBjects property is processed to produce each response’s jSon representation.

<pre>Public Sub testNoPop()
    Dim cr As cRest, jo As cJobject
    Set cr = restQuery("Geocoding", "yahoo geocode", , "input address", , , , False)
    If Not cr Is Nothing Then
        For Each jo In cr.jObjects
            Debug.Print jo.Serialize(True)
        Next jo
    End If
End Sub</pre>

here is a small snippet of the output, cleaned up by the cJobject.Serialize method.

         "ErrorMessage":"No error",
         "Results":[            {
               "line3":"AB35 5TB",
               "line4":"United Kingdom",
               "postal":"AB35 5TB",
               "country":"United Kingdom",
               "uzip":"AB35 5TB",


Being able to access the cJobjects collection is probably the most useful capability in this section. 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 Using cRest directly