Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
badges earned

Your Points Tracker
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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


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"


toJson = """" + key + """ : """ + value """"

End If

End Function


Any help would be very much appreciated

0 answers

Suggest an answer

Log in or Sign up to answer

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you