How to get the Sprint ID using REST API in Excel VBA?

[Fabiano Augusto]

Hi, everybody! 

I’m trying to get the active Sprint ID using REST API in VBA Excel. I successfully got authentication, but when I try to get the Sprint ID, I have got the following message:

{"message":"Client must be authenticated to access this resource.","status-code":401}

Why do I getting this error once I have got a status = 200 in authentication request? Does somebody could show what I am doing wrong? This is my code:

Option Explicit

Sub GetSprintID()

	Dim rc As Integer
	Dim JiraLogin As String
	Dim JiraPassword As String
	Dim JiraURL As String
	Dim JiraBoard As String
	Dim sOutput As String
	Dim sprintOutput As String
	Dim sCookie As String
	Dim oJiraAuth As MSXML2.ServerXMLHTTP60
	Dim oJiraService As MSXML2.ServerXMLHTTP60

	Set oJiraAuth = New MSXML2.ServerXMLHTTP60
	Set oJiraService = New MSXML2.ServerXMLHTTP60

	JiraLogin = "myLogin"
	JiraPassword = "myPassword"
	JiraURL = "myJiraURL"
	JiraBoard = "myBoard"

	With oJiraAuth
		.Open "POST", JiraURL + "/rest/auth/1/session", False
		.setRequestHeader "Content-Type", "application/json"
		.setRequestHeader "Accept", "application/json"
		.send "{""username"" : """ & JiraLogin & """, ""password"" : """ & JiraPassword & """}"
		sOutput = .responseText

		If .Status = "200" Then
			sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira"
		Else
			rc = MsgBox("Fail of authentication.", vbOKOnly + vbCritical, "Error")
			Exit Sub
		End If
	End With

	With oJiraService
		.Open "GET", JiraURL + "/rest/agile/1.0/board/" + JiraBoard + "/sprint?state=active", False
		.setRequestHeader "Content-Type", "application/json"
		.setRequestHeader "Accept", "application/json"
		.setRequestHeader "Set-Cookie", sCookie
		.send
		sprintOutput = .responseText
	End With
	Sheets(1).Range("A1") = sprintOutput

	With oJiraAuth
		.Open "DELETE", JiraURL + "/rest/auth/1/session", False
		.send
	End With

End Sub

Best regards!

Fabiano