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

[Joe Gigliotti]

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

[MR2001]

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

  sUrl = "https://alphajira01.am1.gac-can.com:1234/"

  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]

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?

[Georges Moubarak]

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]

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

[Petar Petrov (Appfire)]

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]

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]

Hi Jeff,

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

[Joe Gigliotti]

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

[Petar Petrov (Appfire)]

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]

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

  sUrl = "https://alphajira01.am1.gac-can.com:1234/"

  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

[Joe Gigliotti]

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]

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.

[JoaoSantos1130142]

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

Any thoughts on that?

[MR2001]

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]

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

[Shuba Narasimhan]

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]

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