Help on VBA program to add an attachment to a Jira issue using api/2/issue/{issueIdOrKey}/attachments

Joe Gigliotti June 10, 2016

I have successfully created an issue however if I want to attach a file to an existing issue I cannot do so using VBA.

I have successfully been able to add a file using the "curl" example here: https://docs.atlassian.com/jira/REST/latest/#api/2/issue/{issueIdOrKey}/attachments-addAttachment as per the example:
"curl -D- -u admin:admin -X POST -H "X-Atlassian-Token: no-check" -F "file=@myfile.txt" http://myhost/rest/api/2/issue/TEST-123/attachments"

It was quite easy using "curl" however I need to do it using Microsoft VB.

I get an error "FileUploadException: the request was rejected because no multipart boundary was found

Now I am new to this and know nothing about what is meant by "multipart boundary" means and I assume "curl" does some of this magic internally?

Any assistance would be greatly appreciated, here is my sample code:

 

Sub JIRA_PostAttachment()

Dim oHttp As Object
Set oHttp = CreateObject("Microsoft.XMLHTTP")

' initialize variables that we will set and pass as parameters
Dim pHtml As String
Dim strResponse As String

pHtml = "https://jira.ae.sda.corp.test.com/rest/api/2/issue/IS-163/attachments"

    '-- prepare the HTTP POST message
    Call oHttp.Open("POST", pHtml, False)
    
    ' Set headers
    oHttp.SetRequestHeader "X-Atlassian-Token", "nocheck"
    oHttp.SetRequestHeader "Content-Type", "multipart/form-data"

    oHttp.SetRequestHeader "Authorization", "Basic Yzc3NjQ2OTpHaWxpdDIwMTY/"
    
    '-- send the message
    Call oHttp.Send("file=C:\Users\c776469\FORM1.msg")
    
    strResponse = oHttp.ResponseText
    MsgBox strResponse
    
    Set oHttp = Nothing

End Sub

5 answers

2 votes
MR2001 June 13, 2016

You can use this VBA code:

 

 Private Sub Test()
  Dim oJiraService As MSXML2.XMLHTTP60
  Const STR_BOUNDARY As String = "abc123-xyz123"
  Dim sUrl As String, sRest As String, sIssueNumber As String
  Dim sFileDataStr As String, sPath As String, sStatus As String
 
  Set oJiraService = New MSXML2.XMLHTTP60
  sIssueNumber = "AMC-7861"
 
  sPath = "C:\Temp\test1.txt"
  sFileDataStr = "--" & STR_BOUNDARY & vbCrLf & _
      "Content-Disposition: form-data; name=""file"";Filename = """ & Mid$(sPath, InStrRev(sPath, "\") + 1) & """" & vbCrLf & _
      "Content-Type: application/octet-stream" & vbCrLf & _
       vbCrLf & GetFileBytes(sPath) & vbCrLf & "--" & _
       STR_BOUNDARY & "--"
 
  With oJiraService
    .Open "POST", sUrl & "rest/api/2/issue/" & sIssueNumber & "/attachments", False
    .setRequestHeader "X-Atlassian-Token", "nocheck"
    .setRequestHeader "Content-Type", "multipart/form-data; boundary=" & STR_BOUNDARY
    .setRequestHeader "Authorization", "Basic " & EncodeBase64("theuser" & ":" & "mypassword")
    .send stringToByteArray(sFileDataStr)
    '.send sData
    sRest = .responseText
    sStatus = .Status & " | " & .statusText
  End With
 
  Set oJiraService = Nothing
end sub 
 
 
Public Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, 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
 
Public Function GetFileBytes(ByVal fPath As String) As String
    Fnum = FreeFile
    Dim bytRtnVal() As Byte
    If LenB(Dir(fPath)) Then ''// Does file exist?
        Open fPath For Binary Access Read As Fnum
        ReDim bytRtnVal(LOF(Fnum) - 1&) As Byte
        Get Fnum, , bytRtnVal
        Close Fnum
    Else
        Err.Raise 53
    End If
    GetFileBytes = byteArrayToString(bytRtnVal)
    Erase bytRtnVal
End Function
 
Public Function byteArrayToString(bytArray() As Byte) As String
    Dim sAns As String
        sAns = StrConv(bytArray, vbUnicode)
    byteArrayToString = sAns
 End Function
 
Public Function stringToByteArray(srcTxt As String) As Byte()
    stringToByteArray = StrConv(srcTxt, vbFromUnicode)
End Function
Jeff Abbott May 18, 2020

Thanks, this appears to be the code I ended up using, Found it or something really close to it online. Is there a way I can include a comment in the POST?

2 votes
Georges Moubarak
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 12, 2016

Hi @Joe Gigliotti,

I have done the same in C#. Hope the below snippet helps:

private static Boolean PostMultiPart(string restUrl, string filePath)
        {
            HttpWebResponse response = null;
            HttpWebRequest request = null;
            try
            {
                var boundary = string.Format("----------{0:N}", Guid.NewGuid());
                var content = new MemoryStream();
                var writer = new StreamWriter(content);
                FileInfo file = new FileInfo(filePath);
               
                    var fs = new FileStream(file.FullName, FileMode.Open, FileAccess.Read);
                    var data = new byte[fs.Length];
                    fs.Read(data, 0, data.Length);
                    fs.Close();
                    writer.WriteLine("--{0}", boundary);
                    writer.WriteLine("Content-Disposition: form-data; name=\"file\"; filename=\"{0}\"", file.Name);
                    writer.WriteLine("Content-Type: application/octet-stream");
                    writer.WriteLine();
                    writer.Flush();
                    content.Write(data, 0, data.Length);
                    writer.WriteLine();
                
                writer.WriteLine("--" + boundary + "--");
                writer.Flush();
                content.Seek(0, SeekOrigin.Begin);
                request = WebRequest.Create(restUrl) as HttpWebRequest;
                if (request == null)
                {
                    return false;
                }
                String username = "npts.integration";
                String password = "integration.npts";
                String encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(username + ":" + password));
                request.Method = "POST";
                request.ContentType = string.Format("multipart/form-data; boundary={0}", boundary);
                request.Accept = "application/json";
                request.Headers.Add("Authorization", "Basic " + encoded);
                request.Headers.Add("X-Atlassian-Token", "nocheck");
                request.ContentLength = content.Length;
                using (Stream requestStream = request.GetRequestStream())
                {
                    content.WriteTo(requestStream);
                    requestStream.Close();
                }
                using (response = request.GetResponse() as HttpWebResponse)
                {
                    if (response.StatusCode != HttpStatusCode.OK)
                    {
                        var reader = new StreamReader(response.GetResponseStream());
                        return false;
                    }
                    return true;
                }
            }
            catch (WebException wex)
            {
                if (wex.Response != null)
                {
                    using (var errorResponse = (HttpWebResponse)wex.Response)
                    {
                        var reader = new StreamReader(errorResponse.GetResponseStream());
                    }
                }
                if (request != null)
                {
                    request.Abort();
                }
                return false;
            }
            finally
            {
                if (response != null)
                {
                    response.Close();
                }
            }
        }
Jeff Abbott May 18, 2020

Thanks, this is very similar to the VBA code I finally found. I have that working now. 

2 votes
Petar Petrov (Appfire)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 10, 2016

The multipart boundary is a string which signifies the boundary between different parts of the multipart request - this tells the server how to read the request. More information can be found here.

Now you are clearly missing the boundary definition in the Content-Type header - you need to add that. You also need to add the actual boundary before sending the file. I'm not quite sure how this is done in VBA, but there are some questions on Stack Overflow that might help you.

Jeff Abbott May 18, 2020

Thanks for the tip. I found an article that demonstrates this in VBA and now have it working.  My new problem is adding a comment to the attachment each time I load it. I suspect I need to use json or xml to accomplish this, but would prefer another method. Curl can do this, but I am working to avoid curl.

Ram Ku June 9, 2020

Hi Jeff,

Could you please share the working VBA code to add attachments. please share.

1 vote
Joe Gigliotti June 11, 2016

Thanks Peter, I read the references you provided about the "multipart request" however I am still struggling with this.

Petar Petrov (Appfire)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 12, 2016

What changes have you made to the code above and what is returned by the server? Did you follow the answer from the stack overflow question? There is a blog post with a VB6 function which does exactly what you need - https://wqweto.wordpress.com/2011/07/12/vb6-using-wininet-to-post-binary-file/.

MR2001 June 13, 2016

Add a reference to Microsoft XML, v6.0 and use:

 

 Private Sub Test()
  Dim oJiraService As MSXML2.ServerXMLHTTP60
  Const STR_BOUNDARY As String = "abc123-xyz123"
  Dim sUrl As String, sRest As String, sIssueNumber As String
  Dim sFileDataStr As String, sPath As String, sStatus As String
 
  Set oJiraService = New MSXML2.ServerXMLHTTP60
  sIssueNumber = "AMC-7861"
 
  sPath = "C:\Temp\test1.txt"
  sFileDataStr = "--" & STR_BOUNDARY & vbCrLf & _
      "Content-Disposition: form-data; name=""file"";Filename = """ & Mid$(sPath, InStrRev(sPath, "\") + 1) & """" & vbCrLf & _
      "Content-Type: application/octet-stream" & vbCrLf & _
       vbCrLf & GetFileBytes(sPath) & vbCrLf & "--" & _
       STR_BOUNDARY & "--"
 
  With oJiraService
    .Open "POST", sUrl & "rest/api/2/issue/" & sIssueNumber & "/attachments", False
    .setRequestHeader "X-Atlassian-Token", "nocheck"
    .setRequestHeader "Content-Type", "multipart/form-data; boundary=" & STR_BOUNDARY
    .setRequestHeader "Authorization", "Basic " & EncodeBase64("theuser" & ":" & "mypassword")
    .send stringToByteArray(sFileDataStr)
    '.send sData
    sRest = .responseText
    sStatus = .Status & " | " & .statusText
  End With
 
  Set oJiraService = Nothing
end sub 
 
 
Public Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, 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
 
Public Function GetFileBytes(ByVal fPath As String) As String
    Fnum = FreeFile
    Dim bytRtnVal() As Byte
    If LenB(Dir(fPath)) Then ''// Does file exist?
        Open fPath For Binary Access Read As Fnum
        ReDim bytRtnVal(LOF(Fnum) - 1&) As Byte
        Get Fnum, , bytRtnVal
        Close Fnum
    Else
        Err.Raise 53
    End If
    GetFileBytes = byteArrayToString(bytRtnVal)
    Erase bytRtnVal
End Function
 
Public Function byteArrayToString(bytArray() As Byte) As String
    Dim sAns As String
        sAns = StrConv(bytArray, vbUnicode)
    byteArrayToString = sAns
 End Function
 
Public Function stringToByteArray(srcTxt As String) As Byte()
    stringToByteArray = StrConv(srcTxt, vbFromUnicode)
End Function
Like john abroham likes this
Joe Gigliotti June 13, 2016

Thanks you so much, I got it to work!

Can I ask a question please, I mostly follow the code however don't quite understand the purpose of the Functions:
GetFileBytes()
byteArrayToString()
stringToByteArray()




MR2001 June 14, 2016

Glad you got it working!

GetFileBytes (uses function byteArrayToString) is reading the file from the disk as a succession of bytes (returns a string).

Once you converted your file to upload into a string you can post it to JIRA using function stringToByteArray that converts the file again into a Byte array.

I give full credit for this code to https://answers.atlassian.com/questions/286877/problems-creating-attachments-with-rest-in-asia where I initially copied it from. It is a little complicated in my opinion, you can probably get rid of both byteArrayToString and stringToByteArray that convert the file back and forth from byte array to string, etc.

 

 

 

Like Lance Ferng likes this
JoaoSantos1130142 October 21, 2016

Well I notice a small diference in file's size before it was sent and after that. 

Any thoughts on that?

 

MR2001 October 21, 2016

Not sure why there may be a difference. We post Excel files to JIRA since a long time using code above, no problems downloading and opening them.

john abroham April 12, 2020

You are a Hero MR2001....!!!!!

0 votes
Shuba Narasimhan January 12, 2018

Hi,

I am looking for VBA code for uploading issues to JIRA. Can anyone point me to some sample code?

 

Thanks in advance & best regards,

Shuba

MR2001 January 12, 2018

This sample shows how to create an issue from Excel VBA to Jira

 

'1. Create the "Cookie"/ session
Set oJiraAuth = New MSXML2.ServerXMLHTTP60
With oJiraAuth
'.setTimeouts 50000, 50000, 50000, 50000
.Open "POST", sURL & "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" '*** Extract the Session-ID
End With

If InStr(1, sOutput, "errorMessages", vbTextCompare) > 0 Then
MsgBox "Cannot connect to JIRA. Please re-enter you password and try again.", vbCritical
GoTo sub_exit
End If

Set oJiraService = New MSXML2.ServerXMLHTTP60
'create an issue - the syntax is case-sensitive
sData = "{""fields"": {""project"": {""key"": """ & sProject & """}, ""summary"": """ &
sIssueSummary & """, ""issuetype"": {""name"": """ & sISSUE_TYPE & """}}}"

With oJiraService
.Open "POST", sURL & "rest/api/2/issue/", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Basic " & EncodeBase64(JIRA_USER & ":" & JIRA_PWD)
.setRequestHeader "Set-Cookie", sCookie '*** see Create a "Cookie"
.Send sData
sOutput = .responseText
sStatus = .Status & " | " & .statusText
End With

If InStr(1, sOutput, "errorMessages", vbTextCompare) > 0 Then
MsgBox "Cannot create JIRA issue. You may not have rights to project " & sProjectFullName & vbCrLf & vbCrLf & sOutput, vbCritical
GoTo sub_exit
End If

Set oJiraService = Nothing
Set oJiraService = New MSXML2.ServerXMLHTTP60

Like lawrence.brodeur likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events