"Run-Time error '13': Type mismatch" in VBA for JSON extraction with JIRA API

Question

New to the community here. I've done a decent amount of programming but I'm completely new to VBA. Never used it before until now and I was tasked with extracting JSON data from a Jira API into an Excel spreadsheet. I keep getting the error "Run-Time error '13': Type mismatch" and I'm not sure why. I know the error has to do with passing in incorrect types but I've tried changing the Json variable to a String with no success. Anyone have any ideas? Thanks!

By the way, this is just a trial Jira instance for testing the API functionality.

Sub test()

'Authenticate the user
Dim response As String

With CreateObject("Microsoft.XMLHTTP")
  .Open "POST", "https://apitestsite.atlassian.net/rest/auth/1/session", False, "admin", "password"
  .setRequestHeader "X-Atlassian-Token:", "nocheck"
  .Send
  response = .responseText
End With

'Query through JSON
Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
MyRequest.Open "GET", "https://apitestsite.atlassian.net/rest/api/2/issue/CC-1", False, "admin", "password"
MyRequest.Send

Dim Json As Object
Set Json = JsonConverter.ParseJson(MyRequest.responseText)

MsgBox Json("fields")("summary")

End Sub

UPDATE: This is where I am at right now. Updated the code for the authentication and now no errors display from the compiler. Here is the JSONConverter class I am using: github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas. The issue now is that the returned JSON string says, "{"errorMessages":["Issue does not exist or you do not have permission to see it."],"errors":{}}". So I am able to connect to Jira just fine and return the JSON as a string, it's just that Jira is rejecting my credentials :/

Private JiraService As New MSXML2.XMLHTTP60
Private JiraAuth As New MSXML2.XMLHTTP60

Sub test()

'Authenticate the user

With JiraAuth
    .Open "POST", "https://apitestsite.atlassian.net/rest/auth/1/session", False
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Accept", "application/json"
    .setRequestHeader "X-Atlassian-Token:", "nocheck"
    .send " {""username"" : ""admin"", ""password"" : ""password""}"""
    sErg = .responseText
    sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/Jira" '*** Extract the Session-ID
End With

With JiraService
    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    MyRequest.Open "GET", "https://apitestsite.atlassian.net/rest/api/2/issue/CC-1", False
    MyRequest.setRequestHeader "Content-Type", "application/json"
    MyRequest.setRequestHeader "Accept", "application/json"
    MyRequest.setRequestHeader "Set-Cookie", sCookie '*** see Create a "Cookie"
    MyRequest.send

    Dim Json As String

    Json = MyRequest.responseText

    MsgBox Json
End With

End Sub

Also, you may want to include code for the JsonConverter class, since that's not a normal refernce that most users would have available to them. — David Zemens
Have you checked the response if it is the expected result? Maybe you get back an HTML response due to an error status? — LocEngineer
Thanks for the replies! The compiler says the error occurs at the last line "MsgBox Json("fields")("summary")". Could I just be filtering through the JSON incorrectly? I'll update my question with the JsonConverter class. — andyp
Ignore the POSTMAN comment from above, just wanted to show that the credentials I am using for my code have access to the API. In terms of recreating my scenario, there isn't much else from the code I provided through VBA. If you would like to access the Jira instance or the JSON that should be displaying, you can login to apitestsite.atlassian.net with "[email protected]" with the password "password". From there you can type in "apitestsite.atlassian.net/rest/api/2/issue/CC-1" in another browser tab to view the JSON for that specific API call. — andyp

David Zemens David Zemens · Accepted Answer · 2017-07-25T17:26:56

This seems to return a valid JSON from the API, which is parseable from the Jsonconverter module.

You were using MyRequest object as possibly the wrong type of object. Elsewhere, you're relying on the MSXML2.XMLHTTP60 class.

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

So I removed the MyRequest and just worked with the JiraService object instead. You had a With JiraService block but you weren't actually using that object at all, you were executing against the WinHttpRequest object within that block.

I also declared all variables, and modified the auth string to use Const strings defined at top of module for user/password.

Option Explicit
Private JiraService As New MSXML2.XMLHTTP60
Private JiraAuth As New MSXML2.XMLHTTP60
Const user As String = "[email protected]"
Const pw As String = "password"
Sub test()
Dim sErg$, sCookie$, Json$

'Authenticate the user

With JiraAuth
    .Open "POST", "https://apitestsite.atlassian.net/rest/auth/1/session", False
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Accept", "application/json"
    .setRequestHeader "X-Atlassian-Token:", "nocheck"
    .send " {""username"" : """ & user & """, ""password"" : """ & pw & """}"""
    sErg = .responseText
    sCookie = "JSESSIONID=" & Mid(sErg, 42, 32) & "; Path=/Jira" '*** Extract the Session-ID
End With

With JiraService
    .Open "GET", "https://apitestsite.atlassian.net/rest/api/2/issue/CC-1", False
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Accept", "application/json"
    .setRequestHeader "Set-Cookie", sCookie '*** see Create a "Cookie"
    .send
    Json = .responseText
End With

Dim j As Object
Set j = JsonConverter.ParseJson(Json)
MsgBox j("fields")("summary")

End Sub

"Run-Time error '13': Type mismatch" in VBA for JSON extraction with JIRA API

1 Answers