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.

enter image description here

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

Comments

Post a Comment

Popular posts from this blog

go - serving up pdfs using golang -

Image
just wondering if guys can code. it works serving images not pdf brochure. in advance func main(){ http.handlefunc("/", func(w http.responsewriter, r *http.request){ filename := "/var/www/filedipenser/brochure.pdf" streampdfbytes, err := ioutil.readfile( filename ) if err != nil { fmt.println(err) os.exit(1) } b := bytes.newbuffer(streampdfbytes) w.header().set("content-type", "application/pdf") if _, err := b.writeto(w); err != nil { fmt.fprintf(w, "%s", err) } w.write([]byte("pdf generated")) }) err := http.listenandserve(":4111", nil) if err != nil { log.fatal("listenandserve: ", err) fmt.println(err) } } the code bit inefficient appears working. however, pdf viewers might sensitive extraneous output adding end of http response stream...
Read more

python - Best design pattern for collection of objects -

i have following architecture: +-----------------+ | collection | | | | | | | | | +-----------------+ | ^ | | create() | | | | v | getitem(b) +------------+ | | item | | | | | | +------+ | | +------------+ a class managing collection of items, creates items. , these items may need other items collection. the actual code in python, , collection passes parameter created item . see it, bad practice. improvement can see pass few functions of collection needed item , instead of whole instance. for example: class collection: getitem(self, id): ... createitem(self, id): item = item(id, self) # <-- pass self ... class item: __init__(self, id, col): ... col.getite...
Read more

how to add preprocess loader in webpack 2 -

i trying add preprocess loader in webpack 3. have installed successfully. not working here's webconfing file var debug = process.env.node_env !== "production"; var webpack = require('webpack'); var path = require('path'); module.exports = { context: path.join(__dirname, "src"), devtool: debug ? "inline-sourcemap" : null, entry: "./js/app.js", module: { loaders: [ { test: /\.jsx?$/, exclude: /(node_modules|bower_components)/, loader: 'babel-loader!preprocess-loader?+debug', // query: { // presets: ['react', 'es2015'], // plugins: ['react-html-attrs', 'transform-class-properties', 'transform-decorators-legacy'], // } }, { test: /\.css$/, loader: "style-loader!css-loader!sass-loader" } ] }, output: { path: __dirname + "/src/", f...
Read more