VBA, the coding language in Excel and Access is not as well suited to calling a JSON REST API as other more modern languages. If you are new to these types of API’s or are new to using them from VBA, this tool will make getting started easier.
There are three simple sample API calls illustrated.
- GET customer. This retrieves the full customer details for a customer based on customer number. This demonstrates creating a filter, encoding that filter, and returning a next VBA dictionary of the results from the LIST endpoint. It then shows how to reference the nested elements of the returned dictionary. This function returns the id of the customer found.
- PUT customer. A PUT is used to update a customer. To issue a PUT you must know the id. This example uses the GET customer above, to first return the id of that customer. A new address is constructed in a VBA dictionary, that dictionary is converted to JSON text, and the result used in the PUT by id.
- POST customer is used to create a new customer. A basic customer name and address are constructed in a dictionary, the dictionary is converted to JSON and that is passed to the API as a POST. The example shows how to read the created id from the http headers.
Spire has a great resource for more information on the Spire API: Spire API Documentation.
You can download the sample XLSM from here: Excel API Samples