10.4 Accessing Web Services and APIs.

In this section, you’ll learn how to interact with web services and APIs (Application Programming Interfaces) using VBA. This allows you to automate tasks that require data from external sources, such as retrieving live stock prices, weather data, or interacting with third-party services like Google Sheets, Slack, or Twitter.

Web services and APIs communicate over the internet and provide a way for applications to exchange data. With VBA, you can use HTTP requests to call APIs, retrieve data, and parse the response.

1. Understanding APIs and Web Services

• API (Application Programming Interface): A set of protocols that allows different software systems to communicate with each other. APIs often return data in formats like JSON or XML.
• Web Services: A type of API that is accessible over the internet. Web services use standard protocols like HTTP and are often used to retrieve data from remote servers.

2. Setting Up for Web Requests in VBA

To interact with APIs using VBA, you need to make HTTP requests. VBA doesn't have built-in support for web requests, so you'll use Microsoft XML, v6.0 library or WinHTTP for this purpose.

Steps to Enable Microsoft XML, v6.0:

1. Open the VBA editor (Alt + F11).
2. Go to Tools > References.
3. Check Microsoft XML, v6.0 (or the highest version available).
4. Click OK.

3. Sending a GET Request

The most common type of HTTP request is the GET request, which retrieves data from an API. You’ll use VBA to make a GET request to a web service, retrieve data, and then process the response.

Example: Making a GET Request to an API

Here’s an example that shows how to retrieve JSON data from a public API, such as OpenWeatherMap (a weather API).

Sub GetWeatherData()
    Dim XMLHTTP As Object
    Dim URL As String
    Dim response As String
    Dim API_KEY As String
    Dim city As String
    
    ' Set API endpoint and parameters
    city = "London"
    API_KEY = "your_api_key_here" ' Replace with your actual API key
    URL = "http://api.openweathermap.org/data/2.5/weather?q=" & city & "&appid=" & API_KEY
    
    ' Create the XMLHTTP object
    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    
    ' Send the GET request
    XMLHTTP.Open "GET", URL, False
    XMLHTTP.Send
    
    ' Get the response as a string
    response = XMLHTTP.responseText
    
    ' Display the response in a message box (just for testing)
    MsgBox response
End Sub

In this example:

• XMLHTTP.Open initializes the GET request to the provided URL.
• XMLHTTP.Send sends the request to the server.
• XMLHTTP.responseText contains the response data in JSON format.

4. Parsing JSON Response

After making the request, the data returned from the API will often be in JSON format. To extract specific values from the JSON response, we can use VBA-JSON, a third-party library that allows easy parsing of JSON.

Steps to Add the VBA-JSON Library:

1. Download the VBA-JSON library from GitHub: VBA-JSON GitHub.
2. In the VBA editor, go to File > Import File and import the JsonConverter.bas file into your project.
3. Once imported, you can use it to parse JSON.

Example: Parsing JSON to Extract Weather Data

Sub GetWeatherDataAndParse()
    Dim XMLHTTP As Object
    Dim JSON As Object
    Dim URL As String
    Dim city As String
    Dim API_KEY As String
    Dim temperature As Double
    Dim weather As String
    
    ' Set API endpoint and parameters
    city = "London"
    API_KEY = "your_api_key_here" ' Replace with your actual API key
    URL = "http://api.openweathermap.org/data/2.5/weather?q=" & city & "&appid=" & API_KEY
    
    ' Create the XMLHTTP object
    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    
    ' Send the GET request
    XMLHTTP.Open "GET", URL, False
    XMLHTTP.Send
    
    ' Parse the JSON response
    Set JSON = JsonConverter.ParseJson(XMLHTTP.responseText)
    
    ' Extract values from the JSON response
    temperature = JSON("main")("temp") - 273.15 ' Convert from Kelvin to Celsius
    weather = JSON("weather")(1)("description")
    
    ' Display the weather data
    MsgBox "The temperature in " & city & " is " & temperature & "°C with " & weather
End Sub

In this example:

• JsonConverter.ParseJson is used to parse the JSON string.
• JSON("main")("temp") extracts the temperature value.
• JSON("weather")(1)("description") extracts the weather description.

5. Sending a POST Request

While GET requests are used to retrieve data, POST requests are used to send data to the server. You might use POST requests to submit form data, create new records in a database, or interact with services that require authentication tokens.

Example: Sending a POST Request

Here’s an example of sending data to an API using a POST request.

Sub SendPostRequest()
    Dim XMLHTTP As Object
    Dim URL As String
    Dim postData As String
    Dim response As String
    
    ' Set API endpoint and data
    URL = "https://jsonplaceholder.typicode.com/posts"
    postData = "{""title"":""foo"",""body"":""bar"",""userId"":1}"
    
    ' Create the XMLHTTP object
    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    
    ' Send the POST request
    XMLHTTP.Open "POST", URL, False
    XMLHTTP.setRequestHeader "Content-Type", "application/json"
    XMLHTTP.Send postData
    
    ' Get the response as a string
    response = XMLHTTP.responseText
    
    ' Display the response in a message box (just for testing)
    MsgBox response
End Sub

In this example:

• XMLHTTP.Open "POST", URL, False initializes the POST request.
• XMLHTTP.setRequestHeader "Content-Type", "application/json" specifies the data format.
• XMLHTTP.Send postData sends the data to the server.

6. Handling Authentication

Some APIs require authentication tokens or API keys for security. You can include the token or key in the request headers to authenticate your requests.

Example: Authentication with an API Key

Sub GetWeatherWithAuth()
    Dim XMLHTTP As Object
    Dim URL As String
    Dim response As String
    Dim API_KEY As String
    
    ' Set API endpoint and API key
    API_KEY = "your_api_key_here"
    URL = "http://api.openweathermap.org/data/2.5/weather?q=London&appid=" & API_KEY
    
    ' Create the XMLHTTP object
    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    
    ' Send the GET request with API key authentication
    XMLHTTP.Open "GET", URL, False
    XMLHTTP.Send
    
    ' Get the response as a string
    response = XMLHTTP.responseText
    
    ' Display the response
    MsgBox response
End Sub

7. Error Handling and Timeouts

When working with APIs, you need to handle potential errors such as timeouts or invalid responses. You can use error handling in VBA to capture and manage these errors.

Example: Basic Error Handling

Sub GetWeatherWithErrorHandling()
    On Error GoTo ErrorHandler
    Dim XMLHTTP As Object
    Dim URL As String
    Dim response As String
    Dim API_KEY As String
    
    ' Set API endpoint and API key
    API_KEY = "your_api_key_here"
    URL = "http://api.openweathermap.org/data/2.5/weather?q=London&appid=" & API_KEY
    
    ' Create the XMLHTTP object
    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    
    ' Send the GET request
    XMLHTTP.Open "GET", URL, False
    XMLHTTP.Send
    
    ' Get the response
    response = XMLHTTP.responseText
    MsgBox response
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Error occurred: " & Err.Description
End Sub

8. Summary of Accessing Web Services and APIs Using VBA

• GET and POST requests: Learn to retrieve and send data using HTTP requests.
• JSON parsing: Use VBA-JSON to parse JSON responses from APIs.
• Authentication: Handle API keys and tokens for secure communication.
• Error handling: Manage errors such as timeouts or invalid responses.

By mastering API interaction with VBA, you can automate tasks that require external data, integrate with other services, and expand the capabilities of your Office applications.