CODEWITHSUNDEEP
vbajirajira-rest-api
Chito
Chito

Reputation: 315

VBA to download macro enabled file (xlsm) from Jira

I am trying to download a macro enabled file (xlsm) from Jira, I have tried the below method and able to download the file but the file is corrupted. Please advice what I am missing here.

Dim myURL As String
myURL = "Put your download link here"

Dim HttpReq As Object
Set HttpReq = CreateObject("Microsoft.XMLHTTP")
HttpReq.Open "GET", myURL, False, "username", "password"
HttpReq.send

myURL = HttpReq.responseBody
If HttpReq.Status = 200 Then
    Set oStrm = CreateObject("ADODB.Stream")
    oStrm.Open
    oStrm.Type = 1
    oStrm.Write HttpReq.responseBody
    oStrm.SaveToFile ThisWorkbook.Path & "\" & "file.xlsm", 2 ' 1 = no overwrite, 2 = overwrite
    oStrm.Close
End If

(source)

Upvotes: 1

Views: 406

Answers (1)

Chito
Chito

Reputation: 315

Finally after several attempts, below code work, hope this is helpful for others.

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

Upvotes: 1

Related Questions