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

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.

 

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

  <span>End</span> <span>With</span>
  
  <span>Set</span> oJiraService = <span>Nothing</span>
<span>End</span> <span>Sub</span>

<span>Private</span> <span>Function</span> EncodeBase64(srcTxt <span>As</span> <span>String</span>) <span>As</span> <span>String</span>
<span>Dim</span> arrData() <span>As</span> <span>Byte</span>
arrData = StrConv(srcTxt, vbFromUnicode)
<span>Dim</span> objXML <span>As</span> MSXML2.DOMDocument60
<span>Dim</span> objNode <span>As</span> MSXML2.IXMLDOMElement

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

EncodeBase64 = objNode.<span>Text</span>

<span>Set</span> objNode = <span>Nothing</span>
<span>Set</span> objXML = <span>Nothing</span>
<span>End</span> <span>Function</span>