Excel, Jira, Rest API end to end example

• Download Jira_Excel_Integration.rar - 478.5 KB
• Download Jira_Excel_Integration.zip - 478.3 KB

Introduction

There is a lot of information on the web about Jira REST API's and do some simple REST calls to Jira from Excel but I have been unable to find a good end to end resource that glues all the concepts together to show one how to login, retrieve data, parse the returned data and insert into excel.  There are some 3rd party for purchase add-in's to excel out there but I wanted a general all purpose add-in for Jira integration with Excel that allowed me to get, update, create, and create word documents ie change request forms from Jira information without having to pay for something.

In this article I will share my learnings, Excel VBA code, and references to others code that provides a fairly elogant tho still in progress semi generic integration between Jira, Excel, and Word.

Background

The first problem I wanted to solve while using Jira was that I wanted to see a list of issues, and their associated issues with the associated issues current status and fixed versions.  I couldn't find away to do this in Jira so I decided to write an excel macro that would get issues from Jira and parse through the associated tickets and list out the status and fixed version so that in a single row/view I could see the status of the ticket and it's associated ticket.  In the image below columns O, P, and Q show the results I was looking for.  And if you look at CLOUD-8050 row you will see how when there are multiple issues linked to an issue how it's displayed in one cell.

After solving that problem I kept extending the Excel macro functionality to create new tickets update tickets and fill in a word document with data from a jira ticket.  The create and update features where more or less a one time need but the form project seemed like on an ongoing generic problem.

While working on this project my biggest issue was that there were plenty of articles out there that talked to single point solutions in using the Jira Rest API from Excel but nothing that really provided an end to end view of how to login, call the api, parse the Json, and the nauances around what I learned.

Using the Excel Jira Integration Macros

In this section I'll document how to use the excel workbook as an end user.  In the points of interest section I'll document the learnings and key code fragments.

The excel work book that comes with this article contains the custom menu additions and the macros both those that I developed on others.  

When you open the workbook there is a new menu on the ribbon called JIRA.  This is a custom menu that calls macros that perform various functions related to retrieving and working with Jira data.

The predominate menus option is "Get Issues" this macro will in turn call Login if the user is not already logged into Jira through Excel.  It will execut the Jira JQL command in column B1 and return the results starting in the table at A8 which is referred to by a defined name in excel as "StartRow".

See the example of the excel workbook in the image above in the background section.

On the macroValues worksheet there is a table that is used to define how to extract the returned data from Jira that is in JSON format so that it can be inserted into the excel worksheet.

The the excel workbook sheet 'Tickets' row 5 which is hidden contains the key for each column.  This key looks up the information int this table on the macroValues worksheet to determone how to extract the data from the Jira Json and if a method should be applied to the data to transform the data.

You can add and remove columns to the Tickets worksheet simply by inserting a new column and indicating what key value is to be used to translate the data with.  You can update the table in the macroValues with your Jira's instance specific custom fields and other fields that you may want to pull into excel by following the format in the table.  Note that customfields will have different values even if you have the same name/label for that field.  These values are unique to each Jira instance.

When you make a change to the table you need to click on the Initialize button in the Ribbon Jira Group.  This dynamically creates a macro called getValue and uses this table to create a sub routine that knows how to reference the desired data from the data returned by Jira.

          

Tools and Dependancies

Before moving on to the points of interest and talking through some of the code.  A number of tools and others code have been used.  So to give credit where credits due...

• Chrome Postman
  • I found the use of the chrome addin postman very valuable in that I could make sure that the basic format and structure of the rest uri was correct and test it in postman.  It was also very useful to be able to see the returned Json so that I could identify the hierarchy structure to know how to access the converted Json object structure. 
  • By sending the rest api string to Jira through postman I can see the raw json returned.  This was the best way to determine what custom fields are labeled with so that you can dereference the returned Json accurately.   You might have to update an existing ticket with some unique identify values so that you can pick out the field in the returned Json.

• Save the username and server URL for the login dialog box uses WriteProp and ReadProp methods that I picked up from the following location.

http://word.mvps.org/faqs/macrosvba/MixedDocProps.htm

• Custom UI Editor for Microsoft Office
  • I found this tool at http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx and utilized it to easily customize the Excel Ribbon Menu.
  • To add my own menu to an excel document I used the UI editor tool to edit and update the excel file so that the JIRA ribbon menu is available when opening the excel file.
• Userful Stuff

There are several methods in the VBA project that start with useful stuff.  Check the following website for details and information on useful stuff.  I copied the fromISODateTime function and created fromISODateTimeNoZ which simply removed the 'Z' from the regular expression in order to parse the dates returned from Jira which were in iso format minus the Z at the end.  At one point I was attempting to use the cJobject provided with mcpher's code however the VBA-JSON project mentioned below was what I ended up using.  There looks like there is alot of cool stuff at ramblings.mcpher.com and worth reading up on.  I imported quite a bit of his code but ended up not use much of it.  Regardless I think there is some really cool concepts and stuff there that merits further investigation.

Licensing and copyrigt information for the useful Info stuff->

• This tool was relatively easy to use but I didn't find much documentation on it's actual usage and had to learn alot through trail and error.  The biggest issue I had was deteremining if a value existed something along the lines of IsNULL.

• The VBA-JSON tool is relatively easy to use

 Set JsonObject = JsonConverter.ParseJson(JsonIssues)

Here the Json string returned from Jira is parsed into an object called JsonObject after it is parsed into an object you can reference the Json as an array of arrays type object.

From the image above of the Json returned in chrome's postman the following references can be made.

dim s as string
s = JsonObject("maxResults") 
s = JsonObject("issues")(1)("key")
s = JsonObject("issues")(1)("fields")("customfield_16730")("value")
s = JsonObject("issues")(1)("fields")("priority")("name")

The trick here is that when a field has sub arrays you have to check for null on the parent area. You can't use isnull(JsonObject("issues")(1)("fields")("priority")("name") you have to check for null on JsonObject("issues")(1)("fields")("priority")

Licensing and copyrigt information for the useful Info stuff->

' VBA-JSON v2.0.1

' (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON
'
' JSON Converter for VBA
'
' Errors:
' 10001 - JSON parse error
'
' @class JsonConverter
' @author tim.hall.engr@gmail.com
' @license MIT (http://www.opensource.org/licenses/mit-license.php)
'' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ '
'
' Based originally on vba-json (with extensive changes)
' BSD license included below
'
' JSONLib, http://code.google.com/p/vba-json/
'
' Copyright (c) 2013, Ryo Yokoyama
' All rights reserved.

Points of Interest

The Basic Code Flow

• Login to Jira
• Make Jira REST API call to return Json
• Convert Json to Object
• Dereference object using table defined in excel
• Insert results in worksheet or word document

Login In

There are numerous examples on the internet for logging in to Jira.  To round out the examples I have added in a couple of features.

• Login Dialog box
• The ability to choose between basic Auth and No Auth
• Saving the username and Jira URL for future logins
• Saving the credentials during the same session so that successive logins are not required

Credentials are stored in a global variable called usernamep if that value is not blank "" then when a call is made to login that value is returend with the previously provided credentials.  Otherwise a dialog box is shown.  If the No Auth check box is marked then usernamep is set to "NoAuth". 

The function GetIssues is provided to show how the login information is used.  It's pretty basic when you put it together.  As you can see if it's NoAuth the header is set to "Authorization","No Auth" otherwise you set the header to basic and use the encoded username password.

Using the usernamep for two purposes is bad programming practice and ideally the authorization value should be a seperate variable all together.  A future version of the code will likely support this so that additional authorization methods can be supported.

             If usernamep = "NoAuth" Then
                .SetRequestHeader "Authorization", "No Auth"
             Else
                .SetRequestHeader "Authorization", "Basic " & usernamep
             End If

Public usernamep As String
Dim url As String
Dim noauth As Boolean

Public Function UserPassBase64() As String
    Dim objXML       As MSXML2.DOMDocument60
    Dim objNode      As MSXML2.IXMLDOMElement
    Dim arrData()    As Byte

    If usernamep = "" Then
        fLogin.Show
        If (fLogin.Cancel = False) Then
            usernamep = fLogin.txtUsername.Value + ":" + fLogin.txtPassword.Value
            url = fLogin.txtServer.Value
            noauth = fLogin.cbNoAuth.Value
            If noauth = True Then
                UserPassBase64 = "NoAuth"
                usernamep = "NoAuth"
            Else
                arrData = StrConv(usernamep, vbFromUnicode)
                Set objXML = New MSXML2.DOMDocument60
                Set objNode = objXML.createElement("b64")
                objNode.DataType = "bin.base64"
                objNode.nodeTypedValue = arrData
                UserPassBase64 = objNode.Text
            End If
        Else
            UserPassBase64 = "cancel"
        End If
    Else
        UserPassBase64 = usernamep
    End If

End Function

Rest Call to Get Issues

Public Function GetIssues(query As String) As String

    Dim JiraService As New MSXML2.XMLHTTP60
    Dim json As Object
    Dim s As String

    usernamep = UserPassBase64
 

    If usernamep = "cancel" Then
        fStatus.AppendStatus ("User canceled login.")
    Else
        fStatus.AppendStatus ("Getting Jira Issues you might see Not Responding in title bar. The time this takes is dependant on the complexity of your query, network, and number of issues being returned.")

        With JiraService
             s = GetUrl + "/rest/api/2/search?jql=" + query + "&startAt=" + _
                        CStr(Range("StartAt").Value) + "&maxResults=" + _
                        CStr(Range("MaxResults").Value)
      .Open "GET", s         
             .SetRequestHeader "Content-Type", "application/json"
             .SetRequestHeader "Accept", "application/json"

             If usernamep = "NoAuth" Then
                .SetRequestHeader "Authorization", "No Auth"
             Else
                .SetRequestHeader "Authorization", "Basic " & usernamep
             End If
             .Send ""

             If .status = "401" Then
                 fStatus.AppendStatus ("Something wrong with query in GetIssues, check your network connection, :  " + .ResponseText)
                 GetIssues = ""
             Else
                 GetIssues = JiraService.ResponseText
             End If
        End With
    End If
End Function

Conver Json to Object and Insert into Worksheet

The more interesting method here is once the Json is returned from Jira the code loops through the rows in the returned Json and the columns in the worksheet.  Using column headers to pull out what data is to be displayed.

The basic flow of this method is:

• delete existing data in the table
• Convert Json String to Object : set JsonObject = JsonConverter.ParseJson(JsonIssues)
• Loop Rows, loop through the number of issues returned in the Json
• Loop Columns and insert the data into the worksheet

Sub ProcessIssues(JsonIssues As String)
    Dim t As ListObject
    Dim z As Double
    Dim JsonObject As Object
    Dim r As Range
    Dim fpath As Range
    Dim s As String

    Set t = ActiveWorkbook.Sheets("Tickets").ListObjects("Table2")

    ' ignoring error if table is already empty
    On Error Resume Next
    t.DataBodyRange.Delete
    On Error GoTo 0
  
  

    Set r = Range("StartRow") ' row to start putting data in
    Set fpath = Range("StartFieldKey") ' Column headings to loop through

    'Convert Json to an object array that makes it easy to access the Json Hierarchy
    fStatus.AppendStatus ("Parsing Returned Json")
    Set JsonObject = JsonConverter.ParseJson(JsonIssues)

    Range("TotalReturned").Value = JsonObject("total")
    fStatus.AppendStatus ("Retrieved " & CStr(JsonObject("total")) & " issue(s) matching query from Jira. Max issues to be retrieved is set at " & CStr(Range("MaxResults").Value) & vbCrLf & " Update maxResults on Trickets sheet to retrieve more.")
    Set issues = JsonObject("issues")
    On Error GoTo gerr

    For z = 1 To issues.count ' row loop: through and process each issue
        fStatus.AppendStatusBar ("Inserting jira issue " & z & " of " & issues.count & " into worksheet.")
        fStatus.progress ((z * 100 / (issues.count)) / 2)
        While (fpath.Value <> "") ' column loop: for each column in excel get the value from the Json Object and put in the cell

            ' Select statement first handles fields that have to have special handling these are fields that have multiple values usually
            ' the case else : calls the default field handling where there is a simple translaction from the Json
            On Error Resume Next
            r.Offset(0, fpath.column - 1).Value = getValue(issues(z), fpath.Value)
            On Error GoTo gerr

            Set fpath = fpath.Offset(0, 1)

        Wend
        Set fpath = Range("StartFieldKey")
        Set r = r.Offset(1, 0)
    Next z
    GoTo finish
gerr:
    fStatus.AppendStatus ("Oops something went wrong: " & vbCrLf & err.description & vbCrLf & " source: " & err.Source & " at row: " & r.row & " column: " & fpath.Value)

finish:

End Sub

getValue

The getValue function is dynamically created from the table on the macroValues worksheet.  From columns D through G.  This allows for the addition of being able to extract data from the Json based on custom field values for each Jira instance that this code may be used against, in that custom field values are unique to a Jira instance.

The CreateCaseSub function creates a new function and dynamically adds it to the excel vba project utilizing the VBProject object references.

The function to create the function has the basic form of creating one long string then calling the appropriate method to insert the string as code.

• Create the function header code
• loop through the table and create the select case statement
• Create the function footer code
• Insert into the VBA Project

Function CreateCaseSub() As VBComponent
    Dim code As String
    Dim r As Range
   
    Set r = Range("getValueStart")
    code = "Public Function getValue(issue as object, key as String) as String" & vbNewLine & _
                vbTab & "dim rvalue as String" & vbNewLine & _
                vbTab & "on error goto gerr" & vbNewLine & _
                vbTab & "Select Case key" & vbNewLine
   
    While (r.Value <> "")
        code = code & vbTab & vbTab & "Case " & """" & r.Offset(0, 0).Value & """" & vbNewLine & _
        vbTab & vbTab & vbTab & "if IsNull(issue" & r.Offset(0, 2).Value & ") then" & vbNewLine & _
        vbTab & vbTab & vbTab & vbTab & "rvalue = """"" & vbNewLine & _
        vbTab & vbTab & vbTab & "else" & vbNewLine


        If r.Offset(0, 3).Value = "" Then
            code = code & vbTab & vbTab & vbTab & vbTab & "rvalue = issue" & r.Offset(0, 1).Value & vbNewLine
        Else
            code = code & vbTab & vbTab & vbTab & vbTab & "rvalue = " & r.Offset(0, 3).Value & "(issue" & r.Offset(0, 1).Value & ")" & vbNewLine
           
        End If
        code = code & vbTab & vbTab & vbTab & "End if" & vbNewLine
        Set r = r.Offset(1, 0)
    Wend
    code = code & vbTab & "End Select" & vbNewLine & _
            vbTab & "getValue = rvalue" & vbNewLine & _
            vbTab & "goto finish:" & vbNewLine & _
            "gerr:" & vbNewLine & _
            vbTab & "AppendStatus (""error getValue issue key: "" & key & "" "" & err.description )" & vbNewLine & _
            "finish:" & vbNewLine & _
            "End Function"
   
   
   
    Dim tempModule As VBComponent
    Set tempModule = ThisWorkbook.VBProject.VBComponents.add(VBIDE.vbext_ComponentType.vbext_ct_StdModule)
    Call tempModule.codeModule.DeleteLines(1, tempModule.codeModule.CountOfLines)
    Call tempModule.codeModule.AddFromString(code)
    tempModule.name = "Module1"
    Set CreateCaseSub = tempModule
End Function

The following code is an example of getValue.  Where for each key in the table the object is checked for a null reference and returns either "" for null or the actualy object data.

At first I used the iif function but it like many trinary operators actually parses the false expression and throws an error if the object can not be referenced thus the more verbose if then else clause had to be used.

Public Function getValue(issue As Object, key As String) As String
    Dim rvalue As String
    On Error GoTo gerr
    Select Case key
        Case "key"
            If IsNull(issue("key")) Then
                rvalue = ""
            Else
                rvalue = issue("key")
            End If
        Case "status"
            If IsNull(issue("fields")("status")("name")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("status")("name")
            End If
        Case "project"
            If IsNull(issue("fields")("project")("name")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("project")("name")
            End If
        Case "summary"
            If IsNull(issue("fields")("summary")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("summary")
            End If
        Case "created"
            If IsNull(issue("fields")("created")) Then
                rvalue = ""
            Else
                rvalue = fromISODateTimeNoZ(issue("fields")("created"))
            End If
        Case "updated"
            If IsNull(issue("fields")("updated")) Then
                rvalue = ""
            Else
                rvalue = fromISODateTimeNoZ(issue("fields")("updated"))
            End If
        Case "assignee"
            If IsNull(issue("fields")("assignee")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("assignee")("name")
            End If
        Case "reporter"
            If IsNull(issue("fields")("reporter")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("reporter")("name")
            End If
        Case "fixversions"
            If IsNull(issue("fields")("fixversions")) Then
                rvalue = ""
            Else
                rvalue = GetFieldsWithCount(issue("fields")("fixversions"))
            End If
        Case "issuetype"
            If IsNull(issue("fields")("issuetype")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("issuetype")("name")
            End If
        Case "labels"
            If IsNull(issue("fields")("labels")) Then
                rvalue = ""
            Else
                rvalue = GetFieldsWithCount(issue("fields")("labels"))
            End If
        Case "prioity"
            If IsNull(issue("fields")("priority")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("priority")("name")
            End If
        Case "severity"
            If IsNull(issue("fields")("customfield_12520")("value")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_12520")("value")
            End If
        Case "resolution"
            If IsNull(issue("fields")("resolution")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("resolution")("name")
            End If
        Case "issuelinks"
            If IsNull(issue("fields")("issuelinks")) Then
                rvalue = ""
            Else
                rvalue = GetFieldsIssueLink(issue("fields")("issuelinks"))
            End If
        Case "businessDriver"
            If IsNull(issue("fields")("customfield_15623")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_15623")
            End If
        Case "businessSignificance"
            If IsNull(issue("fields")("customfield_12420")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_12420")
            End If
        Case "description"
            If IsNull(issue("fields")("description")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("description")
            End If
        Case "detailedDescription"
            If IsNull(issue("fields")("customfield_15621")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_15621")
            End If
        Case "billable"
            If IsNull(issue("fields")("customfield_15926")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_15926")(1)("value")
            End If
        Case "LOE"
            If IsNull(issue("fields")("customfield_15629")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_15629")
            End If
        Case "approvalRequired"
            If IsNull(issue("fields")("customfield_16320")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_16320")("value")
            End If
        Case "approvedBy"
            If IsNull(issue("fields")("customfield_11622")) Then
                rvalue = ""
            Else
                rvalue = issue("fields")("customfield_11622")("displayName")
            End If
    End Select
    getValue = rvalue
    GoTo finish:
gerr:
    AppendStatus ("error getValue issue key: " & key & " " & err.description)
finish:
End FunctionPublic Function 

Inserting Data into Word

The code to insert into word is similar.  It also uses getValue to translate the key value stored in the word custom form field tag property into the data used to insert into the word template.  The basic flow for creating a word templates is:

• select word template and provide Jira Ticket Key
• open word template
• retrieve Jira data for that one ticket
• loop through the custom form fields
• match tag to key value and insert data

The following code does the parsing.  After having prompted for the template and key, retrieving the Json and converting it to an object this code loops through the word form fields. 

Sub FilloutWordTemplateWithJiraData(ByRef o As Object, template As String, s As fEnhancement)


    Dim w As Word.Application
    Dim wd As Word.Document
    Dim jiraKey As String   
    Dim oCC As ContentControl
    Dim i As Integer
    Dim lc As Boolean
   
     
    s.status.Caption = "Opening Microsoft Word"
    Set w = CreateObject("Word.Application")
    w.Visible = True
       
    s.status.Caption = "Opening template: " & template
    Set wd = w.Documents.Open(template)
    'wd.SaveAs MyDocuments() & "\" & jiraKey


    i = 1
    For Each oCC In wd.ContentControls
        strText = ""
        s.status.Caption = "Update form field " & i & " of " & wd.ContentControls.count
        i = i + 1
        Debug.Print oCC.tag
       
        strText = getValue(o, oCC.tag)
        lc = oCC.LockContents
     
        oCC.LockContents = False
        Select Case oCC.Type
            Case wdContentControlCheckBox
               
                If strText = "Yes" Then
                   oCC.Checked = True
                End If
               
            'Case wdContentControlDate ' todo: determine if special date handling needs to be added
            Case default
               
                If strText <> "" Then
                    oCC.Range.InsertAfter strText
                    oCC.SetPlaceholderText , , strText ' if placeholder text exist this removes it so that a clean document without unwanted texted is provided.
                End If
           
        End Select
        oCC.LockContents = lc
    Next oCC
    s.status.Caption = "Finished updating " & i & " fields in template."
  Exit Sub
End Sub

Parsing the Jira Json Learnings

...

   "customfield_13624": null,
    "customfield_15926": [
      {
        "self": "https://jira.mycompany.com/rest/api/2/customFieldOption/16883",
        "value": "Yes",
        "id": "16883"
      }
    ],
    "cus....

Creating the Excel Menus

It's pretty cool that you can create custom menus for each excel file.  Note this is not an add in of a permante nature.  The menu only appears when you have the associated excel file open and it has the focus.

To use the Custom UI Editor for Microsoft Office you simple open the excel file in the tool, note: the excel file cannot be open in excel, create the XML and save the file.  If there are errors in your XML the menu simple will not appear no error messages are displayed.  For this reason adding elements on at a time is recommended. 

Instead of trying to create custom icon's for my custom menu I picked from the stock set of office icon's.  I utilized the reference at the following link to browse through the available icons. 

http://soltechs.net/CustomUI/imageMso01.asp

One of the more annoying learnings I discovered is that the UI editor appears to load the excel macros in addition to the menus into it's memory.  So if you open the UI editor open an excel file and udpate the xml then save it, but keep the excel file open in the UI editor, any chances you make to your macros in excel are overwritten if you come back to the UI editor and make changes and save again.  So the learing open the excel file in the UI editor make your changes then close the file then open in excel.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
 <ribbon>
  <tabs>
   <tab id="customTab" label="JIRA" insertAfterMso="TabView">
    <group idMso="GroupClipboard" />
    <group idMso="GroupFont" />
    <group id="customGroup" label="Jira Tools">
     <button id="customButton5" label="Login" size="large" onAction="Login" imageMso="TableIndexes" /> 
     <button id="customButton4" label="Get Issues" size="large" onAction="GetTickets" imageMso="CacheListData" />    
     <button id="customButton1" label="Get Linked Status" size="large" onAction="UpdateStatus" imageMso="AccessRelinkLists" />
     <button id="customButton3" label="Create Tickets" size="large" onAction="CreateTickets" imageMso="QueryShowTable" />
     <button id="customButton2" label="Update Dashboard" size="large" onAction="UpdateDashboard" imageMso="CreateForm"/>    
     <button id="customButton7" label="Create Enhancement" size="large" onAction="CreateEForm" imageMso="ColumnsDialog"/>
    </group>
   
   </tab>
    <group idMso="GroupEnterDataAlignment" />
    <group idMso="GroupEnterDataNumber" />
    <group idMso="GroupQuickFormatting" />
  </tabs>
 </ribbon>
</customUI>

Random Thoughts

At first I utilized the excel status bar in the lower left hand corner and updated the excel cursor to a xlWait but realized that the status bar was kind of out of the way and not as noticeable.  So I popuped up a dialog box with a status label to provide feed back to the user as to the progress.  This seemed to be more user friendly and user informative.  I also decided not to change the cursor in that the dialog blocked the user from doing anything until the macro was finished and provide the visual feedback needed.   I have found that when you try and update the cursor manually there are almost always unforseen scenarios that leave the cursor in an inaccurate state.

It took quite a bit of playing around with vbCrLf and commas before and after strings to line up the Linked Status and Linked Fixed Versions to align with in the cell to the associated linked tickets.  There is likely a better way to handle this but for now the brute force method of string manipulation worked.

History

4/13/2016: Editted article to give additional credit to http://word.mvps.org/faqs/macrosvba/MixedDocProps.htm

Updated attached excel workbook to include fix in GetIssues .send "" to only be .send as reported by Dirk_B