none
QueryTable refresh generates two HTTP GETs RRS feed

  • Question

  • The following VBA in Excel 2007 generates a call to a perl script on the host. It works exactly as intended, except it consistently generates two calls. First the code:

    With ActiveSheet.QueryTables.Add( _
        Connection:="URL;" & sabBaseURL("wxi" & commandName) & "&g=" & Gen & "&fieldname=" _
        & Application.WorksheetFunction.VLookup(FieldName, prefSheet.Range(SAB_WB_FLD_RNG), SAB_WB_FLD_OFF) _
        & "&value=" & FValue & "&mr=" & MR, Destination:=prefSheet.Range(SAB_WB_RES_RNG))
        .RefreshStyle = xlOverwriteCells
        .Refresh BackgroundQuery:=False
    End With

    The call to the host seems correct, as evidenced by the Apache access_log entries:

    135.245.10.3 - pmr [08/May/2013:10:01:03 -0400] "GET /wsab/run/wximrgedit.pl?prod=sab&g=v8.1&fieldname=ae&value=0.06&mr=sab110047.01 HTTP/1.1" 200 333
    135.245.10.3 - pmr [08/May/2013:10:01:07 -0400] "GET /wsab/run/wximrgedit.pl?prod=sab&g=v8.1&fieldname=ae&value=0.06&mr=sab110047.01 HTTP/1.1" 200 333

    Using breakpoints, I've verified that the HTTP requests are generated from only that one line of code,  and that only one querytable exists. Yet the refresh always makes the two calls, which in turn causes two executions of the perl on the host.

    (I tried moving the ".refresh" method out of the "With".  As expected, the ".add" does nothing without the ".refresh".  And when called as a standalone method, the ".refresh" also generates two hits to the server).

    Does anyone know why I can't get it to issue just ONE of those perfect calls rather than two? Is this some dumbishness on my part?



    Thursday, May 9, 2013 2:04 PM