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:
- 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.
- 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
- 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.