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

new community here. i've done decent amount of programming i'm new vba. never used before until , tasked extracting json data jira api excel spreadsheet. keep getting error "run-time error '13': type mismatch" , i'm not sure why. know error has passing in incorrect types i've tried changing json variable string no success. have ideas? thanks!

by way, trial jira instance testing api functionality.

sub test()  'authenticate user dim response string  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  '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 object set json = jsonconverter.parsejson(myrequest.responsetext)  msgbox json("fields")("summary")  end sub 

update: @ right now. updated code authentication , no errors display compiler. here jsonconverter class using: github.com/vba-tools/vba-json/blob/master/jsonconverter.bas. issue returned json string says, "{"errormessages":["issue not exist or not have permission see it."],"errors":{}}". able connect jira fine , return json string, it's jira rejecting credentials :/

private jiraservice new msxml2.xmlhttp60 private jiraauth new msxml2.xmlhttp60  sub test()  'authenticate user  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 session-id end  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 "cookie"     myrequest.send      dim json string      json = myrequest.responsetext      msgbox json end  end sub 

this seems return valid json api, parseable jsonconverter module.

you using myrequest object possibly wrong type of object. elsewhere, you're relying on msxml2.xmlhttp60 class.

set myrequest = createobject("winhttp.winhttprequest.5.1") 

so removed myrequest , worked jiraservice object instead. had with jiraservice block weren't using object @ all, executing against winhttprequest object within block.

i declared variables, , modified auth string use const strings defined @ top of module user/password.

option explicit private jiraservice new msxml2.xmlhttp60 private jiraauth new msxml2.xmlhttp60 const user string = "jiratestemail82@gmail.com" const pw string = "password" sub test() dim serg$, scookie$, json$  'authenticate user  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 session-id end  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 "cookie"     .send     json = .responsetext end  dim j object set j = jsonconverter.parsejson(json) msgbox j("fields")("summary")  end sub