Viewed 52k times
Is there a plugin or library that could be used to access restful APIs from excel (probably using macros) and then store the responses somewhere (probably in a sheet).
Pardon the missing sample code. I'm not a VBA programmer.
add a comment
You can use the MSXML library within VBA. Then you can create an XMlHTTP request and do a GET or POST etc. Here's a code sample below. It uses late binding i.e. no need to reference the library first:
Option Explicit
Sub Test_LateBinding()
Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://jsonplaceholder.typicode.com/posts/1"
blnAsync = True
With objRequest
.Open "GET", strUrl, blnAsync
.SetRequestHeader "Content-Type", "application/json"
.Send
'spin wheels whilst waiting for response
While objRequest.readyState <> 4
DoEvents
Wend
strResponse = .ResponseText
End With
Debug.Print strResponse
End Sub
I'm using this testing website - JSONPlaceholder - to call a RESTful API. This is the response:
Note that I found that calls to this website with this method fail if you a) make a synchronous request, or b) use
http not https.
- Thanks Robin I'll try this today – AndroidMechanic - Viral Patel Jul 8 '16 at 3:50
- @Robin Mackenzie I understand the answer explains how to obtain some information from Jira, but also the question is about how to put in an excel sheet. I am wondering how the response obtained in Json can be used to write it in the sheet. Usually, jira user interacts with JIRA using JQL, I guess it would be useful to send a query and then to put the response in a sheet. – David Leal Feb 9 '19 at 5:53
- 3@DavidLeal - once you have the JSON you can parse/ manipulate/ update sheets etc - see this question for example: stackoverflow.com/questions/50068973/json-vba-parse-to-excel – Robin Mackenzie Feb 9 '19 at 10:13
- Nice solution Robin. I knew Excel VBA can pretty much do anything, and this saves me from having to learn JS again. Which would be incredibly useful of course, but not something I have time for right now. – Rick Henderson Jun 12 '19 at 14:09
Here, you can find a detailed example using REST API for getting the information from JIRA and loading into excel worksheet. The excel file with the macros can be downloaded from the post. Anyway, it includes a detail explanation on each step in case you want to do it your self for your specific purpose. It's not worth repeating here. I was looking for a similar solution and this is so far, the only post I have found that covers from end to end this problem.
- 2Admittedly this example is a long, in-depth example using JIRA, but it looks like it covers a lot of things people ought to know. Great work David. – Rick Henderson Jun 12 '19 at 14:06
Arquitecto especialista en gestion de proyectos si necesitas desarrollar algun proyecto en Bogota contactame en el 3006825874 o visita mi pagina en www.arquitectobogota.tk
