Access JIRA with VBA Excel 2010


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): 


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


8 answers

0 vote

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 
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


@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.

Add this line of code (in bold)

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

Sorry, both lines must be added.

Thanks M.R. 

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published yesterday in Jira Service Desk

Wy are we still using email for Service Desk workflows?

...attest to the experience of an urgent approval that gets lost in the boss’s inbox and requires that special “Please Approve” email or text message. In an age where we have distributed teams...

114 views 0 2
Read article

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