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 showcase
Posted Friday in United States

Confluence Security Advisory

Good morning Members, Not sure if you are aware. Please read the following: https://www.icloud.com/keynote/0RyJ4VEdNUpjDpGfMhfZx9fEg#Confluence_Security_Advisor More details: https://co...

75 views 1 0
View post

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