Updating/editing Jira issue with REST API with variable URI using VBA

L L_4smmw0

I've modified Fabiano's solution here to create Jira issues in Excel/VBA for the rows selected in a spreadsheet; it worked out well and I was able to create issues from Excel just fine.

Now I want to be able to modify the issues in Excel. So I tried the following, but it seems like it is not even accessing the URI. As you can see, the URI is varying each time because every row has different Jira issue information (for example, row 2 is for TEST-111, row 3 is for TEST-112, ....)   

<span>Sub</span> updateIssue()

    <span>Dim</span> id <span>As</span> <span>String</span>
    <span>Dim</span> rng <span>As</span> Range
    <span>Dim</span> rs <span>As</span> <span>Long</span>

    <span>Dim</span> JiraService <span>As</span> <span>New</span> MSXML2.XMLHTTP60
    
    <span>Dim</span> ResponseTxt <span>As</span> Variant
    <span>Dim</span> Summary <span>As</span> Variant
    <span>Dim</span> Description <span>As</span> Variant
    <span>Dim</span> Project <span>As</span> Variant
    <span>Dim</span> IssueType <span>As</span> Variant
    <span>Dim</span> IssueData <span>As</span> Variant
    <span>Dim</span> Path <span>As</span> Variant
    <span>Dim</span> UN <span>As</span> Variant
    <span>Dim</span> PW <span>As</span> Variant
    <span>Dim</span> Assignee <span>As</span> Variant
    <span>Dim</span> sEncbase64Auth <span>As</span> Variant
    <span>Dim</span> IssueKey <span>As</span> Variant
    <span>Dim</span> IssueAddress <span>As</span> Variant
    <span>Dim</span> IssueURL <span>As</span> Variant
    
    
    
    UN = Sheet2.Range(<span>"A4"</span>).Value
    Pw = Sheet2.Range(<span>"A5"</span>).Value
            
    <span>For</span> <span>Each</span> rng <span>In</span> Selection.Rows
        rs = rng.Row
    
        Summary = ActiveSheet.Cells(rs, <span>4</span>).Value    <span>'summary</span>
        Description = ActiveSheet.Cells(rs, <span>5</span>).Value     <span>'Description</span>
        IssueType = ActiveSheet.Cells(rs, <span>9</span>).Value      <span>'IssueType</span>
        Assignee = ActiveSheet.Cells(rs, <span>8</span>).Value     <span>'assignee</span>
        IssueKey = ActiveSheet.Cells(rs, <span>14</span>).Value     <span>'issue key# in the format of TEST-111</span>
        IssueAddress = <span>""" + "</span>https://your-domain.atlassian.net/jira/rest/api/latest/issue/<span>" + IssueKey + """</span>
                 
        IssueData = <span>" { ""update"": {}, ""fields"" : { ""parent"" : { ""key"" : ""TEST"" }, ""summary"" : """</span> & _
                Summary & <span>""", ""description"" : """</span> & Description & <span>""", , ""assignee"" : { ""name"" : """</span> & Assignee & <span>""" }} } "</span>
          
        sEncbase64Auth = CommonFunction.EncodeBase64(UN & <span>":"</span> & Pw)

        ActiveSheet.Range(<span>"G6"</span>).Value = IssueData & <span>" | Auth Basic: "</span> & sEncbase64Auth

    <span>'Updating/editing issue     </span>
        <span>With</span> JiraService
            .Open <span>"POST"</span>, IssueAddress, <span>False</span>
            .SetRequestHeader <span>"Content-Type"</span>, <span>"application/json"</span>
            .SetRequestHeader <span>"Accept"</span>, <span>"application/json"</span>
            .SetRequestHeader <span>"X-Atlassian-Token"</span>, <span>"nocheck"</span>
            .SetRequestHeader <span>"Authorization"</span>, <span>"Basic "</span> & sEncbase64Auth
            .Send (IssueData)
            ResponseTxt = .ResponseText

            
        <span>End</span> <span>With</span>
        
        ActiveSheet.Cells(rs, <span>8</span>).Value = ResponseTxt
         

        <span>Set</span> JiraService = <span>Nothing</span>
        
    <span>Next</span>

<span>End</span> <span>Sub<br><br><br></span>

<span>' Module CommonFunction</span>
<span>Public</span> <span>Function</span> EncodeBase64(<span>text</span> <span>As</span> <span>String</span>) <span>As</span> <span>String</span>
    <span>Dim</span> arrData() <span>As</span> <span>Byte</span>
    arrData = StrConv(<span>text</span>, vbFromUnicode)
 
    <span>Dim</span> objXML <span>As</span> MSXML2.DOMDocument
    <span>Dim</span> objNode <span>As</span> MSXML2.IXMLDOMElement
 
    <span>Set</span> objXML = <span>New</span> MSXML2.DOMDocument
    <span>Set</span> objNode = objXML.createElement(<span>"b64"</span>)
 
    objNode.DataType = <span>"bin.base64"</span>
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.<span>text</span>
 
    <span>Set</span> objNode = <span>Nothing</span>
    <span>Set</span> objXML = <span>Nothing</span>
<span>End</span> <span>Function</span>

 

I think the parsing part is where it's causing the error, as I do not see ResponseTxt returning anything. How can I solve this?