How To Call Rest Api From Excel Vba And Parse Json Example

How to Call REST API from Excel VBA and Parse JSON Example

In order to call a REST API from Excel VBA and parse the JSON response, you can follow the steps below:

1. Enable “Microsoft WinHTTP Services, version 5.1” library:
   • Open the Visual Basic Editor in Excel by pressing the Alt + F11 keys.
   • Go to Tools > References.
   • Scroll down and check “Microsoft WinHTTP Services, version 5.1”.
   • Click OK.
2. Write VBA code to call the REST API:

   Sub CallAPI()
       Dim objHTTP As Object
       Dim response As String
       
       Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
       
       ' Replace the URL with your REST API endpoint
       objHTTP.Open "GET", "https://api.example.com/endpoint", False
       
       ' Set any required headers (e.g. authentication)
       objHTTP.setRequestHeader "Authorization", "Bearer YOUR_TOKEN"
       
       objHTTP.send
       
       response = objHTTP.responseText
       
       ' Parse the JSON response
       ' You can use various methods to parse JSON in VBA, e.g. JSONConverter, ScriptControl, etc.
       
       ' Example using JSONConverter (requires reference to "Microsoft Scripting Runtime" library)
       Dim json As Object
       Set json = JsonConverter.ParseJson(response)
       
       ' Access specific data from the JSON object
       MsgBox json("key1") ' Replace "key1" with the actual key in the JSON response
       
       ' Alternatively, you can loop through the JSON object and extract all the data
       
       ' Clean up
       Set objHTTP = Nothing
   End Sub

3. Execute the VBA code to call the REST API and parse the JSON response:
   • Alt + F8 to open the Macro dialog.
   • Select the “CallAPI” macro.
   • Click Run.
   • The VBA code will send a GET request to the specified URL and retrieve the JSON response.
   • You can then parse the JSON response using the method of your choice (e.g. JSONConverter).
   • In the example code above, it accesses a specific data point in the JSON response using a key.