Access JIRA with VBA Excel 2010

Hello,

I am trying to access JIRA with VBA to export data to Excel. So, I start with the authentification and then try to export data using this code :

Private JiraService As New MSXML2.XMLHTTP60
Private JiraAuth As New MSXML2.XMLHTTP60

Sub JIRA()

  With JiraAuth
       .Open "POST", "https://jiralink/rest/auth/1/session", False
       .setRequestHeader "Content-Type", "application/json"
       .setRequestHeader "Accept", "application/json"
       .send " {""username"" : """username""", ""password"" : """password"""}"""
       MsgBox .Status
If .Status = "200" Then
sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/" & sPfad
Login = True
End If
End With With ActiveSheet.QueryTables.Add(Connection:= _ "URL;https://jiralink/sr/jira.issueviews:searchrequest-excel-all-fields/temp/SearchRequest.html?jqlQuery=project+%3D+NAME+AND+Sprint+%3D+1+ORDER+BY+priority+DESC%2C+updated+DESC&tempMax=1000" _ , Destination:=Range("$A$1")) .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub

I would like to have this result (screenshot): 

ExportJIRA.PNG

Can anyone help me please ? Sorry, i am new to VBA and JIRA 

 

8 answers

Add this line of code (in bold)

.setRequestHeader "X-Atlassian-Token", "nocheck"
.setRequestHeader "Origin", "your url: port"

@M.R.

Hello, i have the code in VBA but I'm still getting this error: "Anonymous users do not have permission to create issues in this project. Please try logging in first."

Set JiraService = New MSXML2.ServerXMLHTTP60
On Error Resume Next
With JiraService
.Open "POST", "https://myurl/rest/api/2/issue/", False
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Accept", "application/json"
.SetRequestHeader "Authorization" & "Basic ", sEncbase64Auth
.SetRequestHeader "Set-Cookie", sCookie
.SetRequestHeader "X-Atlassian-Token", "nocheck"
.SetRequestHeader "Origin", "myurl: port"
.Send (sData)
sRestAntwort = .ResponseText
sStatus = .Status & " | " & .StatusText
End With

Set JiraService = Nothing

 

If you have any thoughts, it would be very helpful.

You must authenticate first, using something like this:

 

Dim oJiraAuth As MSXML2.ServerXMLHTTP60 
Dim oJiraService As MSXML2.ServerXMLHTTP60

Set oJiraAuth = New MSXML2.ServerXMLHTTP60
With oJiraAuth
.Open "POST", "https://jiralink/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.Send " {""username"" : """ & JIRA_USER & """, ""password"" : """ & JIRA_PWD & """}"
sOutput = .responseText
sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira" 
End With
0 votes

Maybe you get results faster with the Better Excel Plugin which exports to Excel and supports VBA, too.

Thanks for your reply, but I do not use plugins, that's why i am using VBA.

Do it this way (note that variable declaration is also changed compared to your code):

Dim oJiraAuth As MSXML2.ServerXMLHTTP60 
Dim oJiraService As MSXML2.ServerXMLHTTP60

Set oJiraAuth = New MSXML2.ServerXMLHTTP60
With oJiraAuth
.Open "POST", "https://jiralink/rest/auth/1/session", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.Send " {""username"" : """ & JIRA_USER & """, ""password"" : """ & JIRA_PWD & """}"
sOutput = .responseText
sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira" 
End With

 Set oJiraService = New MSXML2.ServerXMLHTTP60
With oJiraService
.Open "GET","https://jiralink/rest/api/2/search?jql=project=YOURPROJECT", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Basic " & EncodeBase64(JIRA_USER & ":" & JIRA_PWD)
.setRequestHeader "Set-Cookie", sCookie 
.Send
sOutput = .responseText
sStatus = .Status & " | " & .statusText
End With

'sOutput  should contain all data in JSON format
'next, you must use a JSON parser to get the desired format

Set oJiraService = Nothing
Set oJiraAuth = Nothing
Private Function EncodeBase64(srcTxt As String) As String
  Dim arrData() As Byte
  arrData = StrConv(srcTxt, vbFromUnicode)
  Dim objXML As MSXML2.DOMDocument
  Dim objNode As MSXML2.IXMLDOMElement
  
  Set objXML = New MSXML2.DOMDocument
  Set objNode = objXML.createElement("b64")
  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  
  EncodeBase64 = objNode.Text

  Set objNode = Nothing
  Set objXML = Nothing
End Function

 

Hi @M.R.

Do you have a code to parse JSON response and access specific fields and display it in a cell?

You can use this VBA module that parses JSON files:

https://github.com/VBA-tools/VBA-JSON

@M.R     I have been using the above format, however I couldn't able to connect to JIRA, always getting 404 error. Kindly let me know if there is any change in the format.

Sorry, both lines must be added.

Thanks M.R. 

It Worked!!! Now I could able to connect to JIRA

Hi Venkatesan,

I have been trying to connect with Jira using the above code but every time it throws the error:

"user defined type not defined" 

Code-

Sub test()
Dim
oJiraAuth As MSXML2.ServerXMLHTTP60  Dim oJiraService As MSXML2.ServerXMLHTTP60 Set oJiraAuth = New MSXML2.ServerXMLHTTP60 With oJiraAuth .Open "POST", "https://jiralink/rest/auth/1/session", False .setRequestHeader "Content-Type", "application/json" .setRequestHeader "Accept", "application/json" .Send " {""username"" : """ & JIRA_USER & """, ""password"" : """ & JIRA_PWD & """}" sOutput = .responseText sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira"  End With
End Sub

It throws the error and highlights the first line:Dim oJiraAuth As MSXML2.ServerXMLHTTP60

I tried adding reference i.e Microsoft Active x Data Objects 6.1 Library but it is still not working.Could you help me in resolving it ,i am very new to VB script.

You must add a reference to Microsft XML, v6.0

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 16, 2018 in Jira

Looking for anyone who made the switch to Data Center

The Jira Marketing team is putting together an ebook on migrating to Data Center. We're looking for pro tips on how you staffed your project team and organized your Proof of Concept. Share yo...

1,392 views 17 10
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