Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal


  • Give kudos
  • Received
  • Given


  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

0 votes


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

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you