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)?
Create Issue via macro from Excel - solved
Moderators: Developer, Contributor
Create Issue via macro from Excel - solved
Last edited by rabbit on 13 Sep 2021, 20:22, edited 1 time in total.
-
- Posts: 10
- Joined: 21 Jul 2021, 22:03
Re: Create Issue via macro from Excel
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
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
Re: Create Issue via macro from Excel
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
***********************************************************
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
***********************************************************
Re: Create Issue via macro from Excel
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!
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!