I'm trying to connect to my Jira project via Excel VBA. The Authentication appears to be successful (status =200) but the response header states <Browser not supported by Jira>. Specifying the same values in the API tool httpie.io successfully returns data. Any obvious issues in my code below?
Sub JiraAuthentication()
Const JiraBaseUrl As String = "https://REDACTED.atlassian.net/rest/api/2/"
Const JiraURL = "https://REDACTED.atlassian.net/rest/api/2/search?jql=project=JOES"
Const JiraUsername As String = "REDACTED@yahoo.com"
Const JiraApiToken As String = "REDACTED"
' --- Construct the Authorization Header ---
Dim AuthString As String
Dim EncodedAuthString As String
AuthString = JiraUsername & ":" & JiraApiToken
EncodedAuthString = EncodeBase64(AuthString)
' --- Example API Request ---
Dim Http As Object
' Set Http = CreateObject("MSXML2.XMLHTTP")
Set Http = CreateObject("MSXML2.XMLHTTP.6.0") 'trying another version
' --- Prepare the Request ---
With Http
'.Open "GET", JiraURL, False
.Open "GET", JiraBaseUrl, False
.setRequestHeader "Authorization", "Basic " & EncodedAuthString
.setRequestHeader "Content-Type", "application/json" ' Or as needed
.Send
End With
' --- Check the Response ---
If Http.Status = 200 Then
Debug.Print "Authentication Successful!"
Debug.Print Http.responseText
Else
Debug.Print "Authentication Failed!"
Debug.Print "Status: " & Http.Status
Debug.Print "Response: " & Http.responseText
End If
End Sub
' --- Helper function to convert to hex ---
Function ConvertToHex(ByVal objByte As Object) As String
Dim i As Long
Dim strHex As String
Dim strTemp As String
strHex = ""
For i = 1 To LenB(objByte)
strTemp = Right("00" & Hex(AscB(MidB(objByte, i, 1))), 2)
strHex = strHex & strTemp
Next i
ConvertToHex = strHex
End Function
