Create Issue via macro from Excel - solved

General discussion of Mantis.

Moderators: Developer, Contributor

Post Reply
rabbit
Posts: 12
Joined: 12 Jun 2021, 06:32

Create Issue via macro from Excel - solved

Post by rabbit »

Hi.

Just a question with an easy answer (yes or no):

Is there a way to create issues to a certain project with a macro from excel?

If yes (which is hopefully the answer), it may turn to a more difficult question: How? Any suggestions?

Greetings,
Rabbit

Edit:
Maybe it helps, if I write down a quick description of my worfklow:

I would like to create a task to someone which has access to mantis but not to my excelfile.
To reduce my workload, I would like to enter specific data for a case then press e.g. a "Create Issue" button in Excel.
After pressing the button, the macro then automatically converts the data to a pdf or to another useful data (yes, I can do that, no help needed) and then create an issue in mantis within a specified project with a specified assigned person.

Can I use the API, but I don't know if the API works both ways (getting and writing data)?
Last edited by rabbit on 13 Sep 2021, 20:22, edited 1 time in total.
rfeldbauer
Posts: 10
Joined: 21 Jul 2021, 22:03

Re: Create Issue via macro from Excel

Post by rfeldbauer »

Hello!
I'm glad you asked this question - it's interesting!
Yes, in Excel's VBA, you can use HTTP request/responses and Mantis' REST API to create an issue.

In your bug tracker, you will need to go to the My Account page (API Tokens tab) and create a token for yourself.

You can delve deeper into the Mantis REST API reference to learn about all you can do:
https://documenter.getpostman.com/view/ ... pi/7Lt6zkP

Sub MAIN()
' set reference to Microsoft XML,v6.0
Dim httpRequest As New MSXML2.ServerXMLHTTP60
' or use late binding...
' Dim httpRequest As Object
' Set httpRequest = CreateObject("MSXML2.ServerXMLHTTP60")

Dim result As String
Dim newIssueData As String
Const bugtrackerBaseURL As String = "http://www.yourcompany.org/tracker/api/rest/issues"
Const token As String = "your_token_here"

' this is example new issue data in json format
newIssueData = "{" & Chr(34) & "summary" & Chr(34) & ": " & Chr(34) & "Sample REST issue" & Chr(34) & "," & Chr(34) & _
"description" & Chr(34) & ": " & Chr(34) & "Description for sample REST issue." & Chr(34) & "," & _
Chr(34) & "additional_information" & Chr(34) & ": " & Chr(34) & "More info about the issue" & Chr(34) & "," & _
Chr(34) & "project" & Chr(34) & ": {" & Chr(34) & "id" & Chr(34) & ": 1," & _
Chr(34) & "name" & Chr(34) & ": " & Chr(34) & "mantisbt" & Chr(34) & "}," & _
Chr(34) & "category" & Chr(34) & ": {" & Chr(34) & "id" & Chr(34) & ": 5," & _
Chr(34) & "name" & Chr(34) & ": " & Chr(34) & "bugtracker" & Chr(34) & "}," & _
Chr(34) & "handler" & Chr(34) & ": {" & Chr(34) & "name" & Chr(34) & ": " & Chr(34) & "vboctor" & Chr(34) & "}," & _
Chr(34) & "view_state" & Chr(34) & ": {" & Chr(34) & "id" & Chr(34) & ": 10," & _
Chr(34) & "name" & Chr(34) & ": " & Chr(34) & "public" & Chr(34) & "}," & _
Chr(34) & "priority" & Chr(34) & ": {" & Chr(34) & "name" & Chr(34) & ": " & Chr(34) & "normal" & Chr(34) & "}," & _
Chr(34) & "severity" & Chr(34) & ": {" & Chr(34) & "name" & Chr(34) & ": " & Chr(34) & "trivial" & Chr(34) & "}," & _
Chr(34) & "reproducibility" & Chr(34) & ": {" & Chr(34) & "name" & Chr(34) & ": " & Chr(34) & "always" & Chr(34) & "}," & _
Chr(34) & "sticky" & Chr(34) & ": false," & _
Chr(34) & "tags" & Chr(34) & ": [{" & Chr(34) & "name" & Chr(34) & ": " & Chr(34) & "mantishub" & Chr(34) & "}]}"

' open your connection
httpRequest.open "POST", bugtrackerBaseURL, False

' set some settings
httpRequest.setRequestHeader "Authorization", token
httpRequest.setRequestHeader "Content-type", "application/json"

' send the request
httpRequest.Send newIssueData

' look at your results (they are in JSON format)
result = httpRequest.responseText
Debug.Print result
End Sub
rabbit
Posts: 12
Joined: 12 Jun 2021, 06:32

Re: Create Issue via macro from Excel

Post by rabbit »

Hi rfeldbauer.

Thanks for your reply. Very helpful! Looks quite simple (except of creating the data ;-) ).

However, I receive a Error 91 (objectvariable not defined) in line httpRequest.Open "POST", bugtrackerBaseURL, False
Even googling it I can't find any reasonable solution.

Autocomplete is suggesting the .open method, so I assume, that the variable httpRequest is correctly defined as MSXML-object and the reference is set correctly as well.
Any ideas?

My code below:
**********************************************************************
Sub test()
' set reference to Microsoft XML,v6.0
Dim test As String
Dim httpRequest As MSXML2.ServerXMLHTTP60

Dim method As String
Dim result As String
Dim newIssueData As String
Const bugtrackerBaseURL As String = "http://[myURL]/api/rest/issues"
Const token As String = "[myToken]"

' this is example new issue data in json format
newIssueData = "{" & _
Chr(34) & "summary" & Chr(34) & ": " & Chr(34) & "Sample REST issue" & Chr(34) & "," & _
Chr(34) & "description" & Chr(34) & ": " & Chr(34) & "Sample REST issue description" & Chr(34) & "," & _
Chr(34) & "category" & Chr(34) & ": {" & _
Chr(34) & "name" & Chr(34) & ":" & Chr(34) & "Rechnung" & Chr(34) & "}," & _
Chr(34) & "project" & Chr(34) & ": {" & _
Chr(34) & "name" & Chr(34) & ":" & Chr(34) & "Verrechnung" & Chr(34) & "}}"


MsgBox newIssueData, vbOKOnly

' open your connection
httpRequest.Open "POST", bugtrackerBaseURL, False

' set some settings
httpRequest.setRequestHeader "Authorization", token
httpRequest.setRequestHeader "Content-type", "application/json"


' send the request
httpRequest.send newIssueData

' look at your results (they are in JSON format)
result = httpRequest.responseText
Debug.Print result
End Sub
***********************************************************
rabbit
Posts: 12
Joined: 12 Jun 2021, 06:32

Re: Create Issue via macro from Excel

Post by rabbit »

Hi rfeldbauer.

Problem solved.


set reference to Microsoft XML,v6.0
Dim httpRequest As New MSXML2.ServerXMLHTTP60
' or use late binding...
' Dim httpRequest As Object
' Set httpRequest = CreateObject("MSXML2.ServerXMLHTTP60")


I had to uncomment the last two lines and to use "late binding", whatever that means.
Furthermore I had to adjust the newissuedata-string creation, because I used custom fields.

For all followers: You'll find some examples how to use custom fields for creating the data here:
https://documenter.getpostman.com/view/ ... 9f9205a954

Thank you rfeldbauer. You've been a great help!
Post Reply