Using VBA to get data from API with bearer token to Excel

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.
  

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
    httpRequest.send
    
    ' 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