Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,364,847
Community Members
 
Community Events
168
Community Groups

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

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

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

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

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();
                }
            }
        }

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

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.

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.

Hi Jeff,

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

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

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/.

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

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()




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

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

Any thoughts on that?

 

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.

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

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

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

Atlassian Community Events