create jira issues using VBA in excel

[fabby]

my jira version is 4.4.1

now i can create jira issue via VBA+SOAP+XML(first shall install soap toolkit ,and invoke msxml dll )

the following code can successfully "create jira issue",but update issue failed.

anyone can give some suggestions on how to edit an issue via excel

Dim token As Variant
    Dim newissue, updateissue As Variant
    Dim issuemap As Variant
    
    Dim b As Boolean
    
    Dim SoapClient
    Set SoapClient = CreateObject("MSSOAP.SoapClient30")
    SoapClient.mssoapinit ("http://192.168.xx.xx:8079/rpc/soap/jirasoapservice-v2?wsdl")
    'token = SoapClient.LogIn("jirauser", "jira")
    token = SoapClient.LogIn("dev", "123")
    
    If Err <> 0 Then
    wscript.echo "initialization failed " + Err.Description
    End If
   
    Dim new_xmlText, update_xmlText As String

    new_xmlText = "<root>" & _
                  "<assignee>" & Range("A2") & "</assignee>" & _
                  "<project>" & Range("B2") & "</project>" & _
                  "<type>" & Range("C2") & "</type>" & _
                  "<summary>" & Range("D2") & "</summary>" & _
              "</root>"
    
    update_xmlText = "<root>" & _
                  "<id>summary</id>" & _
                  "<values>" & Range("F3") & "</values>" & _
              "</root>"
              
    Dim new_xmlDoc As New MSXML2.DOMDocument30
    
    new_xmlDoc.async = False
    new_xmlDoc.LoadXML (new_xmlText)
    If (new_xmlDoc.parseError.ErrorCode <> 0) Then
      Dim myErr
      Set myErr = new_xmlDoc.parseError
      MsgBox (myErr.reason)
      GoTo MyEnd
    Else
     'MsgBox new_xmlDoc.xml
    End If
     
    Dim update_xmlDoc As New MSXML2.DOMDocument30
    
    update_xmlDoc.async = False
    update_xmlDoc.LoadXML (update_xmlText)
    
    Dim new_XMLNodeList As MSXML2.IXMLDOMNodeList
    Dim new_root As MSXML2.IXMLDOMElement
    
    Set new_root = new_xmlDoc.DocumentElement
    Set new_XMLNodeList = new_root.ChildNodes
    
    'Set newissue = SoapClient.createIssue(token, new_XMLNodeList)
    Dim FieldValues(1 To 1) As MSXML2.IXMLDOMNodeList
    
    Dim update_XMLNodeList As MSXML2.IXMLDOMNodeList
    Dim update_root As MSXML2.IXMLDOMElement
    
    Set update_root = update_xmlDoc.DocumentElement
    Set update_XMLNodeList = update_root.ChildNodes
    
    Set FieldValues(1) = update_XMLNodeList
    Set updateissue = SoapClient.updateissue(token, Range("C3"), FieldValues)

the updateissue error is :

org.xml.sax.SAXException: Found character data inside an array element while deserializing

i've learnt jira update issue syntax

RemoteIssue

updateIssue(java.lang.String token, java.lang.String issueKey, RemoteFieldValue[] actionParams)

is it that array and list's failed transfer cause the error or others?

[Nic Brough -Adaptavist-]

Yes, because Jira has a REST interface, and you can post things into it via that.

From memory, I don't think VBA has REST off-the-shelf, but it's easy to add

[fabby]

i installed Microsoft Soap Toolkit 3.0 and try to call jira api from VBA using SOAP.

Dim SoapClient
    Set SoapClient = CreateObject("MSSoap.SoapClient")
    SoapClient.mssoapinit ("http://localhost:8079/rpc/soap/jirasoapservice-v2?wsdl")
    SoapClient.ConnectorProperty("AuthName") = "jirauser"
    SoapClient.ConnectorProperty("AuthPassword") = "jira"

when i debug,error says :

runtime error '-2147352567 (80020009)':

Client:This property name is not recognized HRESULT=0x80070648

[fabby]

i change SoapClient.ConnectorProperty("AuthName") = "jirauser" to SoapClient.ConnectorProperty("AuthUser") = "jirauser".it is ok.but i don't know how invoke "createIssue()" method.

and the syntax for vba to invoke 'login' method

java.lang.String login(java.lang.String username, java.lang.String password)

[fabby]

token=SoapClient.login("jirauser","jira") can login.

but there seems some errors with jira soap apis.the method "getIssue" seems doesn't work.

and i don't know how to invoke createIssue method.

[fabby]

hi, Nic

REST interface is available to JIRA 4.4.1?i find REST is only for JIRA 5.0 and later on Atlassian official website

[Nic Brough -Adaptavist-]

No, it's available from at least 4.2, and I think 4.0

[fabby]

hi,Nic

i try to create issue according to jira REST documention

curl -D- -u jirauser:jira -X POST --data test.txt -H "Content-Type: application/json" http://192.168.xx.xx:8079/rest/api/2/issue/

the output is

HTTP/1.1 404 Not Found
Server: Apache-Coyote/1.1
X-AREQUESTID: 1027x705x1
Set-Cookie: JSESSIONID=7B2DA300EADB51B33114926A76D60DE6; Path=/; HttpOnly
X-Seraph-LoginReason: OK
Set-Cookie: atlassian.xsrf.token=BG95-0HJD-8TXN-OKMW|a7fa53ab282213254db957191db01d32eb44cd70|lin; Path=/
X-AUSERNAME: jirauser
X-ASESSIONID: 1ci0dic
Content-Type: text/html;charset=utf-8
Content-Length: 1006
Date: Mon, 13 Aug 2012 09:07:15 GMT

<html><head><title>Apache Tomcat/6.0.32 - Error report</title><style><!--H1 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:22px;} H2 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:16px;} H3 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:14px;} BODY {font-family:Tahoma,Arial,sans-serif;color:black;background-color:white;} B {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;} P {font-family:Tahoma,Arial,sans-serif;background:white;color:black;font-size:12px;}A {color : black;}A.name {color : black;}HR {color : #525D76;}--></style> </head><body><h1>HTTP Status 404 - /rest/api/2/issue/</h1><HR size="1" noshade="noshade"><p><b>type</b> Status report</p><p><b>message</b> <u>/rest/api/2/issue/</u></p><p><b>description</b> <u>The requested resource (/rest/api/2/issue/) is not available.</u></p><HR size="1" noshade="noshade"><h3>Apache Tomcat/6.0.32</h3></body></html>

the result is of course wrong.but i don't know how to use the curl tool.

[Fabiano Nascimento Leite]

Hi fabby,

See an example - CURL:

curl -D- -u user:pass -X POST -H "X-Atlassian-Token: nocheck" -F "file=@c:\test.txt" http://<jira_url>/rest/api/2/issue/<issue_key>/attachments

See this link, "how to create issue using VBA":

https://answers.atlassian.com/questions/180214/creating-jira-issue-from-excel-vba

[Arek Patek]

Hi!

This code works great, creates an issue in JIRA.

(Set newissue = SoapClient.createIssue(token, new_XMLNodeList))

Does anyone know how to add the attachment to created issue by using:

SoapClient.addAttachmentsToIssue()