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

Question

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:///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: This page is used to hold your data while you are being authorized for your request.

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.

Satyabrat Sahoo · Answer

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

It would be of great help. Thanks

Prashanth Anbazhagan · Answer

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.

Warren_mz9b4z · Answer

Hi

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"
 Else
 .SetRequestHeader "Authorization: ", "Basic " & UserNameP
 End If
 .Send

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

End Function

and

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