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

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

VBA can't log in Jira

Hello Jira Community

I am trying to develop a little Excel tool to access to Jira's table and hopefully be able to modify several records at once depending on epic and other criterias.

But for the moment I can't log in to the database.

it always return to VBA "Login Failed" even if I followed the code proposed here : https://community.atlassian.com/t5/Jira-questions/Access-JIRA-with-VBA-Excel-2010/qaq-p/590003

Is it possible that there is a setting that has to be enabled to authorize external connections? (this is a cloud jira)

 

Best regards

SebPOur Jira.png

1 answer

Hi @sdeclercq 

The password to authenticate in a Jira Cloud is your JIRA API Token and not your enterprise password. Similarly, your username is your Email Address that you used to login into Jira Cloud.

You can generate one here:

https://id.atlassian.com/manage-profile/security/api-tokens

Pass this token as your password & your email address as your username.

Let me know if this helps.

 

Thanks

- Aditya

I know this issue was posted a while back, but it is worth a shot.  Aditya, you mentioned that the pwd = JIRA API Token and the user the email address. Does this information needs to be "enconded" as well?

Yes, you'll need to do a base64 encoding for both.

token = Base64(Email Address + ":" + Jira API Token)

Pass the token generated above in the Authorization header.

thank you for replying to my old question.

After some tweaking I succeeded to connect last year and indeed, I had to use the base64 encoding.

Here is my code (I use the "interface" worksheet to store my URL, ID and token.)

--------------------------------------


Dim JiraService As New MSXML2.XMLHTTP60
Dim JiraAuth As New MSXML2.XMLHTTP60
Dim Réponse As String

Dim Fint As Worksheet
Set Fint = ThisWorkbook.Worksheets("Interface")
Dim XURL As String
Dim XLogin As String
Dim XToken As String
XURL = Fint.Range("URL")
XLogin = Fint.Range("Login")
XToken = Fint.Range("Token")


With JiraAuth


.Open "GET", XURL & "/rest/api/2/search?jql=filter=" & XFiltre & "&startAt=" & StartAt, False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "User-Agent", "machin" 'ajouté par moi pour que ca marche
.setRequestHeader "Authorization", "Basic " & EncodeBase64(XLogin & ":" & XToken)
.send

Réponse = .responseText
If .Status <> "200" Then
      MsgBox ("Erreur " & .Status)
      Exit Sub
End If

------------------------

and the encodebase 64  est cette fonction:

----------------


Public Function EncodeBase64(srcTxt As String) As String
Dim arrData() As Byte
arrData = StrConv(srcTxt, vbFromUnicode)
Dim objXML As MSXML2.DOMDocument60
Dim objNode As MSXML2.IXMLDOMElement

Set objXML = New MSXML2.DOMDocument60
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData

EncodeBase64 = objNode.Text

Set objNode = Nothing
Set objXML = Nothing
End Function

Thank you very much for spending time to send me this information. It works like charm!

At first I thought it didn't, but then I realized data is in JSON. My application is XML ready; I have all kinds of methods and processes to read XML files but not JSON.

So my question would be, is there a way to retrieve data XML format? or... should i look into building a mechanism to read JSON.

Nevertheless I appreciate your help immensely.

I made a mechanism to read Json, so I don't know about the XML.. sorry

No need to be sorry. You have already helped me a lot. It got my code going and my brain is in working mode. There is an XML API request to which I am looking at:

[JRASERVER-67095] JIRA XML API takes a long time to return XML data (search request) - Create and track feature requests for Atlassian products.

So far I am in reading mode... when I get it going I'll post it! I'll make sure I'll mention your code!

Cheers!

To write, you will need this approach: (this one is used to change the status of a task identified by its key)

Texte = "{""transition"":{""id"":""" & Nbstatus & """}}"
.Open "POST", XURL & "/rest/api/2/issue/" & key & "/transitions", False ' & "/transitions?expand=transitions.fields", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "User-Agent", "machin" 'ajouté par moi
.setRequestHeader "Authorization", "Basic " & EncodeBase64(XLogin & ":" & XToken)
.send Texte

 

'error management
If Err.Number = -2147467260 Then
Err.Clear
GoTo suite
End If
Réponse = .responseText

 

If .Status <> "204" Then
.Login = True
MsgBox (Réponse & Chr(10) & "Erreur " & .Status)
Exit Sub
End If

suite:

Suggest an answer

Log in or Sign up to answer
TAGS

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