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,
- find the Rest URl , try it out, and understand what the response looks like
- call restQuery() with appropriate arguments
- You should consider committing that research to the library at this point in case you need it again
- Why not submit it to our forum for inclusion in the library so that others can benefit from your research.
Now let’s look at Populating sheets with Rest reponses