Today copied the listobject table pre-sizing 'feature' into the Cache update routine and on the go could get rid of several made-redundant variables [why define when it was already defined;p). Reusable code again, but so small it was easier to just copy and adapt to the data on hand rather than making it a reusable module.

And, another vexing issue resolved, an age old peeve... the statusbar not updating progress after a while. Tried lots over the years, but now reverted to something in an MSDN article of 2002 [declared expired, supplied as-is]. After each statusbar progress message tell the macro to microsecond pause and allow the system to do it's thing [updating the screen]. Much lit about 'don't use', but it proved innocuous in testing... the statusbar now refreshes properly after each 250 records import with simple 'DoEvents' instruction. A few extra seconds processing time on minutes waiting and stressing if it runs or not taken away [on 9000 active records in the RS pages]

Hasta la croque monsieur.

P.S. Allowing DoEvents also lets through a few cycles to BOINC in case it feels starved and wants to quite jobs when the hunting is taking all the juice for a few minutes [when loaded to the brim] :P)

And so we have reverted to a much simpler binary, boolean, yes/no, true/false all pushed into a code mask, which has just one outcome again, 1 or 0, 1 is credit, 0 is no credit.

Count	EOGVTA	Credit Yes/No (1/0)	Mask Status description
1816	111111	1	New Valid, This Period Modtime
7	111110	0	Old Valid, This Period Modtime, On Archive
0	111101	1	Old Valid, Previous Period Modtime, Not on Archive
3184	111100	0	Old Valid, Previous Period Modtime, On Archive
427	110001	0	Old Pval
38	110011	0	New PVal
24	110401	0	Old Pver
28	110411	0	New Pver
181	030201	0	Old Server/User Aborted
18	030211	0	New Server/User Aborted
2	140001	0	Old No Reply
2	140011	0	New No Reply
2	160201	0	Error Validation
5729			Total Count, Has to be equal to total active results

Each digit in a combination representing a state

Statûs Codes Explanation
E=Exit Status 1=No Error 0=Error
O=Outcome 1=No Error, 3=Error, 4 NoReply, 6=Error Validation, 7=Aborted
G=Granted 1=Yes, 0=No
V=ValidationState 1=Valid, 2=Invalid, 3 TME, 0/4=PV, 5=TooLate
T=ModTime Current 1=Yes 2=No
A=Archive 1 =No, 0=Yes

Instead of 6 nested condition checks, now a simple 1 or 0 index/match lookup, on the fly, of the generated mask with

=IF([@ModTm]=0;"";IF([@ExitStatus]=0;1;0)&[@Outcome]&IF([@Granted]>0;1;0)&[@ValState]&IF([@ThisPer];1;0)&IF([@NotArch];1;0))

Why checking the 'granted'... it's the only giveaway that I can determine that some faulty results were still courtesy credited... the 'Error Validation for instance and the Too Many Error take outs.

The power pivot revealed only 13 code combinations occurred on near 6000 results [5729, which matched to the ResultsAvailable field, of which only 2 codes lead to credit, 111111 and 111101. Monitoring with a unique code list generator ATM if new ones show up in the coming weeks, but it wont be many [I hope].

Spanner insert is what was long suspected... as the program fetches 250 max at a time, transacting continues, so the next 250 may in fact have shifted one or more up or down in the order, and you miss to fetch those records, or get duplicates. The archiving would not take the duplicates, but the missing, code 111101 were never, and not counted, because the ModTime was of a previous period. More drawingboard work to do. :(, but we did discover 4 records not counted, still on the RS pages, accounting for some of the 'WCG shows more credit than computed locally', and they now forward on are' :).

Hasta la penne aldente

If you have trouble with duplicate records in the result fetches, use

ISNA(MATCH([@Name];OFFSET(NameCol;0;0;ROW()-1;1);0))

This digs through all the records from the top [NameCol], at offset 0,0 through to the previous last record [Row() - 1]. If ISNA is false i.e no match found, it's not a duplicate, if true, print 'Duplicate' instead of testing if this record needs inclusion in the current period based on the mask... no further testing if false of course, as that would be a waste of crunching cycles.

Unfortunately =IFNA is not a function available in Office 2010 and before, so can't use that, but it would be a simpler test / outcome function.... still trying to maintain backward compatibility.

Every knife has two sides to the blade .

P.S. On the outside chance, it could happen that the first of the duplicates has a newer timestamp than the second [newest at top fetch sort does not resolve this] and was validated in the few seconds between the first and the second pass... really off chance and need to see it happen before considering it worth another test level, if seeking more than perfection

(The paella is simmering on the stove)

36 hours on FAHB and 4 new masks [6 digit number combos] were found, nicely listed out with the array function, nicely hotlinked to the offending results, but, frowning on those 110201's as they are not being credited, 2 is clearly the 'Invalid indicator for the 4th digit... hmmm.

The top off for today is, found some code at Contextures that could be adapted to purpose-name PivotTable drilldown reports [to investigate the heretic results], which then on closing the tool raises the 'To Delete or No Delete, That's the Question', if there are any 'DrillIn_Sheet#' pages, so next time the Hunting tool is opened, no relics are lingering.

Great was a discussion stumbled over at MrExcel about attaching hyperlinks Screentips to shapes [the Action Buttons], and then discovering the assigned macro would no longer execute. One was wondering if setting an event call off to the hyperlink address into a unused cell and launch the macro from there, but never got back. Wellllll, I tried and indeed this works great... now even the floating action bar can carry it's pop-up comment boxes, albeit just one-liners long as the screen is wide... better a bird in the hand than 10 in the air. What has me flummoxed though is, the hyperlinks calling the macro-event cell only executes one time. Only when saving the book, closing and opening will they work again, 2-3 times and then they die again. Is there any secret liaison between WCG and MSN ;?

Hasta la...

Very very, very very close to complete automation of the fetch url formulation... desperate seeking susan solution, resorted to the .sendkey feature as there is [apparently intentionally], no code to modify the data connection web address url edit box. Now a formula is composing the urls with offset, member name, account key etc as per API, converts it to text, copies this into the buffer, then the script sends keys to do Alt Data Connection Properties Edit and a ^v~ to paste the string into the right box.

Surprisingly landed in .Net for the code as VBA does not have (documented) certain features such as wait and 'repeat any key n times'.

"SendWait(String)

Sends the given keys to the active application, and then waits for the messages to be processed."

Miraculously they work in Office 2016, then tried 2013, then tried 2010 and still working.

So here we are, good old keystroke macro execution, hahaha.

Sub ResultQueryAddress() 'ShortCut Ctrl+r
    
    With Application
        Dim addr As String
        '.Run "ScreenOff"
        MsgBox "Next, when web-screen appears, do Import > OK > Close"
        [ResQryDyn].Calculate 'Update dynamic http qry address based on present parameters, member/acckey/offset
        addr = [ResQryAddress].Value
        .StatusBar = "Address, " & addr
        .Wait (Now() + TimeValue("00:00:00"))
        Range("ResQryAddress").Copy 'Takes the dynamic http address and stores it to clipboard
        .Goto ("ResQryAddrFix") 'move to cell where to put the text value of previous line
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        'Range("ResQryAddrFix").Activate
        .Wait (Now() + TimeValue("00:00:01"))
            .SendKeys ("{F2}+{Home}"), True 'Clipboard_GetText
            .SendKeys ("^c{Esc}{Del}"), True 'Copy the full http text in [ResQryAddrFix]
            .SendKeys [ResQryKeys].Value, True 'Execute keystrokes stored in [ResQryKeys] cell.
            DoEvents
            '.Wait (Now + TimeValue("0:00:05")) 'Give it time to fetch the objective Results set
            AppActivate Application.Caption
            '.SendKeys ("+{Tab 7}"), True
           .SendKeys ("%I{Enter}%C"), True 'goes to the sheet instead of the web connection address screen... argh.
        DoEvents
        .ScreenUpdating = True
        .StatusBar = False
    End With

End Sub

Sadly though the focus is lost when the web xml is pulled and the Import > OK > Close is ending in limbo, then gets processed in the sheet instead of the frame when done manually. AppActivate is no solution either as when having multiple workbooks open you end up in the wrong one [rolling dice]...agrh.

More drawingboard work, but we are down from originally 30 minutes per page to create to 10 minutes, to now 10 seconds, to close one with a manual %I(Enter}%C

Hasta la calamares fritos