andyp andyp - 3 years ago 234
JSON Question

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

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

Answer Source

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

enter image description here

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.

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 = "jiratestemail82@gmail.com"
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download