Jira: Add multiple links using R4J Excel Add-In and VBA macro

I share in this post a way to add multiple links to Jira issues using R4J Excel Add-In and additional VBA macros. This post is a DRAFT/WIP quick version. If you have questions about it, please leave a comment to let me know and I will update it.

1. Step-by-step tutorial
2. See also
   1. R4J MS Excel Plugin - R4J Data Center - Requirements Management for Jira - Confluence
   2. Jira: Create multiple issue links by script

Step-by-step tutorial

• Import the issues you want to edit in Excel using the R4J Excel Add-In-> Load Data
• select the 'Issues Links' Field to be imported.
• Format the Data as Table for easy filtering
• Filter for the issues you want to add links to
• You can add a new e.g. 'Selected' Column for manual selection
• Run the Macro
• It will create a copy of the 'Issue Links' column named 'New Issue Links'
• Filter for the issues you want to synchronize back, by adding for example a calculated "Diff" column
• Remove unfiltered issues to only write back modified issues example using a macro included below
• Replace the original 'Issue Links' column by the 'New Issue Links' (make a backup to document; delete 'Issue Links' Column, rename 'New Issue Links' into 'Issue Links')
• Upload data using the Excel Add-In function

The Macro to Add Links looks like below:

	Sub ILV_AddLinks()
	' This function will add Link(s) to column named "New Issue Links" column
	' It runs only for visible / unfiltered rows
	' User will ask to input link:issuekey to add
	' User can enter multiple links, comma separated
	Dim sheet As Worksheet
	Set sheet = ActiveSheet
	' colLinks
	Set cl = sheet.Rows(1).Find("New Issue Links")
	If cl Is Nothing Then
	' Duplicate Issue Links
	Call ILV_DuplicateColumn
	End If
	Set cl = sheet.Rows(1).Find("New Issue Links")
	If cl Is Nothing Then
	MsgBox "New Issue Links column not found!", vbOKOnly, "Error"
	Exit Sub
	End If
	colLinks = cl.Column
	' Link Input
	sLinks = InputBox("Please enter 'link:issuekey' to add (csv)")
	If Len(sLinks) = 0 Then ' User cancelled
	Exit Sub
	End If
	' Loop for all Rows in UsedRange - visible/ unfiltered only
	For Each Row In sheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows
	' Skip header first row
	If (cnt = 0) Then
	GoTo SkipRow
	End If
	NewIssueLinksValue = Row.Cells(1, colLinks).Value
	IsModified = False
	For Each sLink In Split(sLinks, ",")
	'Add a reference to Microsoft VBScript Regular Expressions 5.5
	' Remove white spaces after :
	With New RegExp
	.Pattern = ":\s*"
	.MultiLine = True
	.Global = True
	sLink = .Replace(sLink, ":")
	End With
	' Remove redundant spaces
	With New RegExp
	.Pattern = "\s{2,}"
	.MultiLine = True
	.Global = True
	sLink = .Replace(sLink, " ")
	End With
	If Not (ILV_IsLink(NewIssueLinksValue, sLink)) Then
	IsModified = True
	NewIssueLinksValue = NewIssueLinksValue & vbLf & Trim(sLink)
	End If
	Next sLink
	ContinueRow:
	' Update New Issue Links column with new value
	If IsModified Then
	Row.Cells(1, colLinks).Value = NewIssueLinksValue
	End If
	SkipRow:
	cnt = cnt + 1
	Next Row
	End Sub
	Sub ILV_DuplicateColumn()
	Dim sheet As Worksheet
	Set sheet = ActiveSheet
	' Duplicate Issue Links column to 'New Issue Links' Column
	Set cl = sheet.Rows(1).Find("Issue Links")
	If cl Is Nothing Then
	MsgBox "Issue Links column not found!", vbOKOnly, "Error"
	Exit Sub
	End If
	colLinks = cl.Column
	Columns(colLinks + 1).Insert
	cnt = 0
	For Each Row In sheet.UsedRange.Rows
	' Skip header first row
	If (cnt = 0) Then
	Row.Cells(1, colLinks + 1).Value = "New Issue Links"
	Else
	Row.Cells(1, colLinks + 1).Value = Row.Cells(1, colLinks).Value
	End If
	cnt = cnt + 1
	Next Row
	End Sub

view raw JiraAddLinks.bas hosted with ❤ by GitHub

See also

R4J MS Excel Plugin - R4J Data Center - Requirements Management for Jira - Confluence

Jira: Create multiple issue links by script