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 

 

5 comments

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

 

@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

Add this line of code (in bold)

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

Comment

Log in or Join to comment
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,314 views 14 20
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot