0

I have inherited an Excel Tracker sheet and the code originally was created by someone else who has since left the company.

We have an Excel VBA Tracker sheet which communicates with Jira and used to work fetching projects and issues without any issues until our company changed from site minder for cookies to "Isolated Site Minder" for more secure cookies, since the change over to the isolated site minder cookie authentication our Excel tracker sheet will fetch the project key from Jira without any problem but it brings the same list of 50 issues no matter which project we are querying Jira for and no matter how we change the code it wont see the issue "Key" and filter and fetch the correct issues for the project. I'm not brilliant as Excel VBA but I do understand and can follow what the code is doing and make small adjustments, but I have now inherited this tracker sheet and am not very good at the Jira code and communicating with each other

the code is as follows:

Public Function httpGet (ByVal url as String) As String()
Dim resultArray(2) as String 
Dim PostData as String
url = baseurl + url
Dim smiwhr As New SMIsolatedWinHttpRequest

  With CreateObject("WinHTTP.WinHTTPrequest.5.1")
    .Option(WinRequestOption_EnableRedirects) = True 
    .Option(WinRequestOption_EnableHttpsToHttpRedirects) = True
    smiwhr.NewRequest "GET", url
    smiwhr.Send PostData

    .Open "GET", url, False
    .SetRequestHeader "Content-Type", "Application/json"
    .SetRequestHeader "Accept", "application/json"
    .SetRequestHeader "Cookie", sCookie
    .Send
    resultArray(0) = Status
    resultArray(1) = .ResponseText

  End With
  httpGet = resultArray
End Function  

There is a function that gets the requests which has the code:

Public Function getRequestByPaging(ByVal projectKey as String, ByVal StartIndex as Integer, Optional maxResults As Integer, Optional fields As String) As Object
  Dim resultArray() As String
  Dim api as String
  Dim jql As String
  Dim PostData As String
  api = "rest/api/2/search"
  jql = "project=" & projectKey & " and issuetype!=\""Project\"" ORDER By Key ASC "
  PostData = "{"
  PostData = PostData + toJson ("jql", jql) + ","
  PostData = PostData + toJson("StartAt", StartIndex) + ","
  If maxResults <> "" Then
    PostData = PostData + toArray("field", fields) + ","
  End If
  PostData = Mid(PostData, 1, Len(PostData) - 1)
  PostData = Post + "}"
End Function  

The jql part does pick up the project key, it is when it sends the details of the project through to the Json that it returns the wrong issue key 

The Json function is as follows:

Public Function toJson (byVal As String, ByVal value As String) As String
  If value = "" Then
    toJson = """" + key + """ : null"
  Else
    toJson = """" + key + """ : """ + value + """"
  End If
End Function

Any help would be very much appreciated

CraZ
  • 1,669
  • 15
  • 24
Phil Worrall
  • 1
  • 1

1 Answers1

0

While editing your original question and adding syntax highlighting to it (otherwise your code cannot be read), I've noticed that the syntax in the function in question is probably wrong:

You had it:

  If value = "" Then
    toJson = """ + key + """ : null"
  '          ^^^ missing "
  Else
    toJson = """" + key + """ : """ + value """"
  '                                         ^ missing +
  End If

It should be this way:

  If value = "" Then
    toJson = """" + key + """ : null"
  Else
    toJson = """" + key + """ : """ + value + """"
  End If
CraZ
  • 1,669
  • 15
  • 24