Hi, @Jayanthi
Not sure about this script, that it will work right as you need (I'm not a fan of VBA), but you can give it a try.
Certainly! Here's a sample VBA code that demonstrates how to create subtasks for an existing parent issue using the Jira REST API in Excel VBA:
```vba
Sub CreateSubtasks()
Dim parentIssueKey As String
parentIssueKey = "PARENT-123" ' Replace with the key of the parent issue
Dim subtaskSummaries As Variant
subtaskSummaries = Array("Subtask 1", "Subtask 2", "Subtask 3") ' Replace with the summaries of your subtasks
Dim subtaskDescriptions As Variant
subtaskDescriptions = Array("Description 1", "Description 2", "Description 3") ' Replace with the descriptions of your subtasks
Dim jiraURL As String
jiraURL = "https://your-jira-instance.atlassian.net/rest/api/2/issue/"
Dim authHeader As String
authHeader = "Authorization: Basic " & Base64Encode("your-jira-email@example.com:your-jira-api-token")
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")
Dim i As Long
For i = LBound(subtaskSummaries) To UBound(subtaskSummaries)
Dim subtaskSummary As String
subtaskSummary = subtaskSummaries(i)
Dim subtaskDescription As String
subtaskDescription = subtaskDescriptions(i)
Dim payload As String
payload = "{""fields"": {""project"": {""key"": ""YOUR-PROJECT-KEY""},""summary"": """ & subtaskSummary & """,""description"": {""type"": ""doc"",""version"": 1,""content"": [{""type"": ""paragraph"",""content"": [{""type"": ""text"",""text"": """ & subtaskDescription & """}]}]},""issuetype"": {""name"": ""Sub-task""},""parent"": {""key"": """ & parentIssueKey & """}}}"
Dim url As String
url = jiraURL
http.Open "POST", url, False
http.setRequestHeader "Content-Type", "application/json"
http.setRequestHeader "Accept", "application/json"
http.setRequestHeader "Authorization", authHeader
http.send payload
If http.Status = 201 Then
MsgBox "Subtask created successfully."
Else
MsgBox "Failed to create subtask. Error: " & http.responseText
End If
Next i
Set http = Nothing
End Sub
Function Base64Encode(ByVal input As String) As String
Dim objXML As Object
Dim objNode As Object
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("base64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = Stream_StringToBinary(input)
Base64Encode = objNode.Text
Set objNode = Nothing
Set objXML = Nothing
End Function
Function Stream_StringToBinary(ByVal s As String) As Variant
Dim ado As Object
Dim stm As Object
Set ado = CreateObject("ADODB.Stream")
ado.Type = 2 ' adTypeText
ado.Charset = "us-ascii"
ado.Open
ado.WriteText s
ado.Position = 0
ado.Type = 1 ' adTypeBinary
Stream_StringToBinary = ado.Read
ado.Close
Set stm = Nothing
Set ado = Nothing
End Function
```
Make sure to replace the following values in the code:
- `parentIssueKey`: The key of the parent issue in Jira.
- `subtaskSummaries`: An array containing the summaries of the subtasks you want to create.
- `subtaskDescriptions`: An array containing the descriptions of the subtasks you want to create.
- `jiraURL`: The base URL of your Jira instance's REST API.
- `authHeader`: Replace `"your-jira-email@example.com:your-jira-api-token"` with your Jira email and API token.
- `YOUR-PROJECT-KEY`: The key of your Jira project.
Please note that this code uses basic authentication with your Jira email and API token. Make sure to replace it with the appropriate credentials and adjust the Jira URL according to your instance.
Before running the code, ensure that you have enabled the "Microsoft XML, v6.0" reference in your VBA editor by going to "Tools" > "References" and checking the corresponding option.
When you run the `CreateSubtasks` macro, it will create subtasks for the specified parent issue using the provided summaries and descriptions. A message box will display the success or failure of each subtask creation attempt.
Remember to customize the code according to your specific Jira configuration and requirements.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.