Adhoc Rest Queries

Page Content hide
1 What can you learn here?
2 Running one off queries using the cRest class get it now
3 Why would you need to do one off calls
4 How to do one off calls
5 Summary
6 Share with your network

What can you learn here?

  • Library of Rest Calls
  • One off queries
  • Argument structure

Running one off queries using the cRest class get it now

Now that we have a generalized mechanism to easily populate Excel tables from Rest queries in Rest to Excel library, let’s take a look at making one off rest calls. 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. 

Why would you need to do one off calls

I think it is better to add all calls to the rest library- for one thing it is a simple way to record the research you would have down for how a query works. However you may not want to mess with the supplied procedures, or you may want to create a form to use the cRest class so that non VBA programmers can use it, but allow them to enter adhoc queries too. 

How to do one off calls

The library allows you to provide a URL and a response format argument rather than relying on them to be supplied inside the library.

  • sRestURL – the URL of the one off query
  • sResponseResults – the description of where to find the reponse data
  • bTreeSearch – as described in Adding to the rest library this will modify the behavior when searching for a column match between the worksheet column names and the response.

 Here are the arguments for each of the functions in restLibrary 

This means we can call it with appropriate arguments, in this example to do a patent search on the term “excel”

Public Sub testOneOff()
Dim cr As cRest
Set cr = restQuery("patent", , "excel", , _
"https://ajax.googleapis.com/ajax/services/search/patent?v=1.0&rsz=8&q=", _
"responseData.results", False)
End Sub

WIth patents as a preset in the library, This would be equivalent to

Public Sub testLibrary()
    Dim cr As cRest
    Set cr = restQuery("patent","google patents" , "excel")
End Sub

Summary

Creating an adhoc query is simply a matter of,

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   Populating sheets with Rest reponses