Excel VBA returning values to multiple cells from XML web query -

-

i have xml data on web. prices , price data of in game item:

https://api.eve-central.com/api/marketstat?regionlimit=10000002&typeid=34

invoke of function single value (for example highest buy order):

           b 1 itemid  buy/max 2  34      =importxml("https:// ... &typeid=34", "//buy/max") 3  35

function looks like:

function importxml(url string, query string)  dim document    msxml2.domdocument60 dim http        new msxml2.xmlhttp60  http.open "get", url, false http.send  set document = http.responsexml  importxml = document.selectsinglenode(query).nodetypedvalue  end function

it's ok 1 value. if got 500 items, getting data extremely long. populate multiple cells (in case two) need modify input link adding &typeid=35:

https://api.eve-central.com/api/marketstat?regionlimit=10000002&typeid=34&typeid=35

with link can 2 values msgbox. need work on data need in cell b3. code looks this:

function importxml(url string, query string)  dim document    msxml2.domdocument60 dim http        new msxml2.xmlhttp60  http.open "get", url, false http.send  set document = http.responsexml  dim r integer dim c integer r = activecell.row                         '<- these variables changing' c = activecell.column                      '<- output cell'  set xmlnodelist = document.selectnodes(query) each xmlnode in xmlnodelist     cells(r, c) = xmlnode.nodetypedvalue   '<- line cause error'     'msgbox xmlnode.nodetypedvalue          <- if line active got correct result'     r = r + 1                                 'but in msgbox'  next xmlnode  end function

when i'm trying write data query cells there problem. i'm trying achieve similar behaviour importxml function google sheets web service. don't have excel 2013 can not use webservice function or filterxml , xpath. have never code in vba. if can put here final code grateful.

for line giving error:

cells(r, c) = xmlnode.nodetypedvalue

can try instead:

cells(r, c).value = cstr(xmlnode.nodetypedvalue)

Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more