Regrettably not been able to get the sendkey sequence to always run it's course 100% of the times... only when the CPU is slowed to 8086, does Excel [which is multi-threaded], execute the final Import > OK > Close keystrokes where they are supposed to happen, in the web address connection edit screen and not in the sheet, messing things up. But, while typing this, I know and have already done in other scripts, the automated switching up and down of allowed threads, so that's the next thing to test.

Mapping was today the numero uno issue, or rather re-mapping. How to ask a question on Google makes a world of difference and so far never found the right order of phrasing. Today though just asked "how to add a field to an xml map", and the Stackoverflow answer coming back was "Add A Field To An Xml Map ", hahahaha. In reading the hopes went up to finally have found the working solution. After the first No No not possible, tried the second sequence which failed, went all the way to the bottom and went through those steps with failed too... maps cannot be modified [incessant "table is already mapped", thoug hte map was unmapped and delete]. Then tried the 3rd solution, hacking into the xml directly... copied file, opened it with 7z, found the xmlMaps.xml map [reply 4], and opened that into a text editor. Cut maticulous, paste in the bits for the extra field, saved. Opened in Excel 2010 and got a 'file damaged, should we try to repair?'. Did that, but no added field. Exited, made a new copy, 7zipped, pulled xmlMaps.xml into an editor, did the same, more rigurous, copying the full code of a known expanded map and put that in place of the one with the field short, saved, opened in Excel 2010... no protests. Went to look for the list of the 120 maps in the book... nada, nothing, zilch, blank... no maps. 3 strikes and you're out, went into amino acid dna cutting and pasting... rinse, repeat and, yes, success, the map showed the extra field. Too cumbersome though, but the reading of the whole Q&A gave me some ideas and opened the workbook directly, unmapped -all- fields of a target xml to table map and created an entirely new one in an empty space, then deleted all the field of the new one in the sheet, which has the same effect as unmapping. Deleted the old map, deleted the old web connect, renamed the new connect to that of the old and mapped the new map to the fields of the old table... no protests still. Ran the refresh and pronto prontissomo, voilà, old table with new field pulled from the recreated xml map.

The typing of this took longer than doing the steps, and now have a working and documented, fast solution to getting the FileDeleteState field quite quickly into the 120 xml maps without messing things up, for that matter knreed can add more fields to the api and my hunting tool wont choke... 3 done, 117 to go.

Meantime the 6 digit masking method to establish 'to credit or not to credit', continues to work a treat. This morning had 3 new combinations to add to the small mask table [now 30 in total]. Really odd is one because 7 with a code -187 upload issue occurred, and one got credited, the other 6 were not. Why at all a credit if upload failed [ExitStatus -187] is a mystery. Anyhow, any new mask comes in pairs, ying and yang, so when in one cycle they are for instance 011211 [where the 2 is the Validation=Invalid], the next cycle it will show as 011200, since the result is still being listed, but the second last 0 now telling it is a case of of the previous period and the last 0 telling the result has been archived in a previous period i.e. mark as 'ready for deletion' as what the FileDeleteState also confirms... it's a 2, the final setting before deleting from the Result Statûs. Short for, this afternoon after running the MyCon update then Archive then Update History, of course the 3 yangs showed up in the active result listings [The summary reports do this for me, i.e. no need to go scanning all records].

End of this day, the hourglass just turned the 20th hour for the day, so tata, see ya tomorrow. The Hasta is now past-a too.

There's so many ways to skin a cat, is said, and that so many is 9. Today I went for the 10th way, getting into Frankenhauser territory, and this time I seem to have hit the jackpot, first a little piece in an exhibition piece of 'how to', dated 2011, then a little piece from a forum post in 2006, and the 'piece de resistance' a post of 2002, using excel 2000... yes we are regressing in time, there where it was revealed that if you insert a for instance a ["offs"] there's a pause for input, into the appropriate places in a URL Query address, and then two-step, to make that ["offs"] look at a field or be fed as a variable that pulls a value in a stepped for-next loop or an array in a sheet, so you can dynamically feed the URL with the member name, account key, results to fetch and the page offset, in short this little diddy

With oSh.QueryTables(tabl)
        .Connection = "URL;" & "https://secure.worldcommunitygrid.org/api/members/" & memnm & "/results?code=" & acck & "&format=xml&limit=" & pagesz & "&offset=" & offs & "&ServerState=5&SortBy=ReceivedTime"
        .Refresh
End With

is injecting the url in the concatenated format needed to convince WCG to give up the results wanted, the number of them and counting from which on the Result Status pages [in xml].

Mind you, the headers of the table are completely fried, and half of the fields are for whatever reason duplicated, so 22 became 34 column table, but it works, consistently... Must have run it 100 times this development session, to get to this point and know it's not like the 9th case of the skinned cat where the last 3 .SendKeys go into space and you sit there staring at the screen asking for you to hit Import > OK > Close, 110 times as that is the number of queries that have been merged to get a maximum of 15000 Results and 14000 buffered In Progress (just in case a cruncher goes wild on the farm ;P) . Now building the loop and the arrays and linking the columns to the appropriate field of the Results table [I seem to remember a Borland product I used in the previous millennium that made this real easy].

ttyl

P.S. This did land me into .Net framework for good, so it says bottom right when loading the query first time... suspiciously close to what I think WCGDAWS was based on, but ICBVR

Yesterdays "Mind you, the headers of the table are completely fried, and half of the fields are for whatever reason duplicated" written during the evening, turned a dawning late at night, when a whole x-mas tree of came on. With the learneth, was actually able to construct a direct injection into the existing table queries and voilà, the

oSh.QueryTables(tabl)
.Connection = "URL;" & "https://secure.worldcommunitygrid.org/api/members/" & memnm & "/results?code=" & acck & "&format=xml&limit=" & pagesz & "&offset=" & offs & "&ServerState=5&SortBy=ReceivedTime"
.Refresh

became

oWb.XmlMaps("ResultsStatus_Map" & i).Import URL:=mymap

... direct injection and refresh in a single code line as part of existing for-next loop, i.e. the account key / member name can be changed on the fly whilst pulling the results data from the website [and this works also during the nightly My Contribution lock-out].

No mapping issues, no recreation, no completely fried table headers, serene processing with a little counter speeding in the status bar telling that the updates per page is faster than the seconds arm moves on the clock.

Now all that's needed is knreed give us the xml code for the device stats, device profiles and my history web pages and this baby can be made public distribution [for the interested who PM me].

This morning took the v2.0c running from top of this thread, which I was stil running as small cruncher and ported the data into v2.0t, latest test-build. Data-transfer was smooth, some 'only one page' conditions needing some pre-emptive error catching and some formula enhancements to convert 'numbers turned text' [the ol pain of decimal comma and decimal point friction] and now large and small account run side by side in the same instance, not 1 dog year point difference.

Now all that's needed is knreed give us the xml code for the device stats, device profiles and my history web pages, and the lynch-pin can be pulled, whence this baby can be made public distribution [for the interested who PM me].



P.S. The QueryTable code is retained, just in case a Back to the Future need develops

Finally had the chance to test against > 18000 live records on the Result Status pages, 'results' that is, not counting the In Progress, so started coding like crazy and got to 17500 results last night the tool can handle. With some new found 'minimalism' coding, this constituting 68 sequential page calls of 250 results, only 24 were missing this morning [and there's a feature to catch those in a next run]. Regrettably overnight many completed results were migrated off to drop the total to 16900, so only caught 23 of the 24 in the 'search-party' run. Under 1 minute to pull the pile now, 1 minute for Results, 1 minute to pull up to 14000 In Progress, reducing chance of doubles and missing greatly. Now is the waiting for the weekend, when the removal of completed results seems to slow down... maybe another chance to get to 18000+, if the OET1s continue to be short

Hasta la Pasta Ricotta/Spinaci/Pancetta [the dish for today]

edit typos

I'm wondering if you are violating the "Communication Guidelines", Rob. It says there:
It's not clear to me what kind of software code they are referring to, but it sounds like they mean ALL software code.

Adri