Excel VBA returns Browser Not Supported by Jira

[Joe Ketchum]

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

[Dirk Lachowski · Accepted solution]

See https://community.developer.atlassian.com/t/jira-issue-retrieval-in-excel-vba-returns-browser-not-supported-by-jira-html-document/45923/6

[Joe Ketchum]

Thanks Dirk.  I found this article as well.  I tried but didn't have any luck with the suggested solution: 

.setRequestHeader “User-Agent”, “Edg / 90.0.818.46”

I did some additional research on User-Agent settings since this solution was from Jan 2022.  In searching I discovered this site with a ton of devices and settings:  https://deviceatlas.com/blog/list-user-agent-strings-2021#desktop 

But, more importantly, on this site I observed that the format did not include any spaces in the specified string.  I tried “Edg/90.0.818.46” and I'm no longer getting the error.  So thanks for sending me back down this path!

[Dag Atle Stenstad]

Hi @Joe Ketchum ! And welcome to the Atlassian Community.

Switch to WinHttp.WinHttpRequest.5.1—it provides more control, especially for authentication and proxy handling, and often works better with external APIs.

 Set Http = CreateObject("WinHttp.WinHttpRequest.5.1")

That will resolve the error message you're getting :)  

[Joe Ketchum]

Thanks for the advice Dag!  Just ran a quick test and that connection is working as well.  Now onto the actual coding.