Using our Excel VBA code to connect to Jira and Isolated Site Minder cookie stopped working

[Phillip Worrall]

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 + "}"

*******************************************************

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