How to download macro enabled excel (xlsm) from Jira using VBA

[Chito Thoudam]

I am new in Jira integration using VBA with excel/access. I am trying to find macro enabled excel (xlsm) file from Jira and download. Let's say file name always starts with "abc". So far I am able to gather some codes from this community and got to the point that I can download a file from Jira. But two issues I am facing, those are,

1. File is corrupted and not getting the actual content
2. I still haven't figured out how to get the file name/url based on file name contains "abc"

Your valuable input will be much appreciated. Code I have got so far,

Sub GetFileFromJira()

Dim FileNum As Long
Dim FileData() As Byte
Dim WHTTP As Object
Dim mainUrl As String
Dim FileURL As String
Dim filePath As String
Dim strAuthenticate As String

mainUrl = "https://MyJiraUrl.com/"
FileURL = "https://MyJiraUrl.com/secure/attachment/1309245/test.xlsm"
filePath = "C:\Users\myId\Downloads\test.xlsm"




strAuthenticate = "start-url=%2F&user=" & "JiraUID" & "&password=" & "JiraPWD" & "&switch=Log+In"

Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

WHTTP.Open "POST", mainUrl, False 'WHTTP.Open "POST", fileUrl, False
WHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.send strAuthenticate

WHTTP.Open "GET", FileURL, False
WHTTP.send

FileData = WHTTP.responseBody
Set WHTTP = Nothing

FileNum = FreeFile
Open filePath For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum

MsgBox "File has been downloaded!", vbInformation, "Success"

End Sub

[Chito Thoudam · Accepted solution]

Finally after several attempts, below code works for the first issue, hope this is helpful for others. I still have to figure out to find the attachment id and file name based on naming convention.

Private Sub DownloadFromJira()
  Dim oJiraService As MSXML2.ServerXMLHTTP60
  Dim sPath As String
  Dim sStatus As String
  Dim FileData() As Byte
  Dim FileNum As Long
  
  Set oJiraService = New MSXML2.ServerXMLHTTP60
  
  sPath = "C:\Users\**ID**\Downloads\Test.xlsm"
  
  With oJiraService
    .Open "GET", "https://**MyJiraLink**/secure/attachment/123455/Test.xlsm", False
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Authorization", "Basic " & EncodeBase64(JiraUID & ":" & JiraPWD)
    .setRequestHeader "Accept", "application/json"
    .send
    
    sStatus = .status & " | " & .statusText
    If .status = "401" Then
        MsgBox "Not Connected"
    End If
    
    FileData = .responseBody
    FileNum = FreeFile
    Open sPath For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
    Close #FileNum

  End With
  
  Set oJiraService = Nothing
End Sub

Private 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