I need to retrieve data from a REST API, and we are going to use VBA to pull data from the web, which requires bearer token authentication.

To use VBA to retrieve data from an API with a bearer token and import it into Excel, you can follow these steps:

  • Declare Variables: Declare variables for storing the API endpoint URL, the bearer token, and the data retrieved from the API.
  • Create HTTP Request: Use the XMLHttpRequest object to create an HTTP request.
  • Set Request Headers: Add headers to the request, including the authorization header with the bearer token.
  • Send Request: Send the request to the API endpoint.
  • Handle Response: Extract the data from the response and import it into Excel.
Here's an example of how you can do this in VBA:

Sub GetDataFromAPI()
    Dim url As String
    Dim token As String
    Dim httpRequest As Object
    Dim responseText As String
    ' Set the API endpoint URL
    url = "https://www.quickpickdeal.com/api/Product/GetAllProducts"
    ' Set the bearer token
    token = "your_bearer_token_here"
    ' Create a new HTTP request
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    ' Open the HTTP request
    httpRequest.Open "GET", url, False
    ' Set the authorization header with the bearer token
    httpRequest.setRequestHeader "Authorization", "Bearer " & token
    ' Send the HTTP request
    ' Get the response text
    responseText = httpRequest.responseText
    ' Handle the response (for example, parse JSON and import into Excel)
    ' Add your code here to parse the response and import data into Excel
    ' For example purposes, display the response text in a message box
    MsgBox responseText
    ' Clean up
    Set httpRequest = Nothing
End Sub