This library was created to provide a simple way to integrate Excel as an OData client for any web service supporting the OData Protocol.
It supports all HTTP Methods (GET, PUT, POST, DELETE, PATCH, HEAD), and allows for easy integration with any API supporting OData.
The library supports the OAuth2 Client-Credentials Authentication flow and takes care of all steps necessary for authenticating the client to the Service Provider, with no configuration necessary from the user.
The package also includes the following resources which can be used as standalone:
- Import the
OAuthClient.cls,libHTTP.bas,libJSON.bas,utilities.basmodules into the VBA Project. - Enable the Reference to
Microsoft XML, v6.0andMicrosoft Visual Basic for Applications Extensibility 5.3from the Tools>References menu.
To perform an (Authenticated) request to an OData service, you need to:
The method signature is the following:
Public Function sendGenericApiRequest(
url As String,
method As httpMethod,
body As String,
ByVal headers As Scripting.Dictionary,
queryParams As Scripting.Dictionary,
Optional userID As String = "",
Optional password As String = "",
Optional forceTokenRefresh As Boolean = False
) As httpResponseThe arguments to this function are:
The function returns a value of the httpResponse type, defined as follows:
Public Type httpResponse
status As httpResponseStatus 'Enum representing the overall status (1XX, 2XX, 3XX, 4XX, 5XX)
statusCode As Integer 'Status Code of the Response
statusText As String 'Status Text of the Response
headers As String 'Headers of the Response
text As String 'Text Content of the Response, as raw text
body() As Byte 'Binary Content of the Response, as Byte array
End TypeFor responses with JSON payloads, the libJSON library provides a simple way of parsing the contents.
The function used to parse a JSON string for a specific value is the following:
Public Function getJSONFieldValue(
jsonString As String,
fieldName As String,
Optional valueIfNotFound As Variant = "",
Optional valueIfNull As Variant = "null",
Optional valueIfNaN As Variant = "NaN
) As VariantThe arguments to this function are:
The type of value returned from this function depends on the type of the field in the JSON Object.
| Value in the JSON Object | Returned Value Type |
|---|---|
| String | String value |
| Numeric (no decimals) | LongLong value |
| Numeric (with decimals) | Double value |
| Boolean | Boolean value |
| null | value defined by the valueIfNotFound parameter |
| NaN | value defined by the valueIfNaN parameter |
| Object | String value representing a JSON encoded Object. If neccessary, it can be further parsed the same way as the original object |
| Array | Array of String Values. In case the array contains JSON Object, each element of the returned array can be further parsed for its fields. |
The function can correctly parse numbers expressed in scientific notation.
Sub sendTestRequest()
Dim url As String
Dim headers As New Scripting.Dictionary
Dim queryParams As New Scripting.Dictionary
'Declare and set a new OAuth client
Dim client as New OAuthClient
client.setClientCredentials "%CLIENTID%", "%CLIENTSECRET%", "%CLIENTENDPOINT%"
'Add Headers and Query Parameters to the request
headers.Add "Sample-Header", "value"
queryParams.Add "$top", 50
queryParams.Add "$count", "true"
'Send the request and get the Response
Dim response As httpResponse
response = client.sendGenericApiRequest(url, httpMethod_get, "", headers, queryParams)
'Check the Response Status
if response.status <> httpResponseOk Then
Debug.Print "Error while processing request. Status code: " & response.statusCode & ", text: " & response.text
Exit Sub
End If
'Process the response JSON content to extract a specific field
Dim requestedValue As String
requestedValue = getJSONFieldValue(response.text, "field1/field2")
Debug.Print "The requested value is " & requestedValue
End Sub