I want to update a JIRA issue throught Excel VBA. I tried to create session cookie.

When I try the below code, it gets 200 success status but it doesnt fetch me jession id.

Public JiraService As New MSXML2.XMLHTTP60
Public JiraAuth As New MSXML2.XMLHTTP60

With JiraAuth
.Open "POST", "https://<jiralink>/secure/jira/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.send "{""username"" : """ & strUsername & """, "" password "" : """ & strPassword & """}"""

Cells(2, 2).Value = .responseText
MsgBox Mid(.responseText, 42, 32)

If .Status = "200" Then
sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/Jira"
End If
End With


Response text:

<HTML><HEAD><TITLE></TITLE></HEAD><BODY onLoad="document.AUTOSUBMIT.submit();">This page is used to hold your data while you are being authorized for your request.<BR><BR>You will be forwarded to continue the authorization process. If this does not happen automatically, please click the Continue button below & form text which has some details.

Help me fetch Jsession id.

1 answer

This widget could not be displayed.


Are you using the Jira username ro the e-mail address for the variable strUsername? It needs to be e-mail address. The name and password need to be encrypted - see my VBA routines

Public Function GetIssues(query As String) As String

' Dim JiraService As New MSXML2.XMLHTTP30
Dim json As Object

If JiraService Is Nothing Then Set JiraService = New MSXML2.XMLHTTP60
UserNameP = UserPassBase64

With JiraService

.Open "GET", query

.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Accept", "application/json"
If UserNameP = "NoAuth" Then
.SetRequestHeader "Authorization", "No Auth"
.SetRequestHeader "Authorization: ", "Basic " & UserNameP
End If

If .Status = "401" Then
GetIssues = ""
GetIssues = JiraService.ResponseText
End If
End With

End Function


Public Function UserPassBase64() As String

Dim objXML As MSXML2.DOMDocument60
Dim objNode As MSXML2.IXMLDOMElement
Dim arrData() As Byte
Dim URL As String

UserNameP = "e-mail:password"
arrData = StrConv(UserNameP, vbFromUnicode)
Set objXML = New MSXML2.DOMDocument60
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
UserPassBase64 = objNode.Text

End Function

No luck. Getting error 404 on trying encryption method.

Other method:

On using username, I get 200 status just that response is different. Even if I use mail id, I get the same response.

Do you have any full excel vba jira connector that will save my time building codes ?

It would be of great help. Thanks

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in Teamwork

What teamwork quotes inspire you?

Hey everyone! My name is Natalie and I'm an editor of the Atlassian Blog and I've got a question for you: What's your favorite quote about teamwork?  We've compiled a list here, along with...

191 views 18 7
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you