Delaying macro until web query fully loaded.

[Captain Sensible]

Is anyone clued up with web queries ? I'm looking for someway to delay the rest of a macro running until the web query has finished loading, obviously I don't want to use some timer as that may mean unnecessary delays or occasions where the web query is slow for whatever reason.

I'm considering maybe a "private sub worksheet_change target as range" kicked off by the data area being populated but surely there must be something within excel to say when the query has been retrieved?

Thanks

[alrodopial]

Code: Select all

Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop

[Captain Sensible]

Thanks I stuck it after the web query code but just got an error "Invalid or unqualified reference"

I'll have a better look after racing see if I can figure out where to stick it in the coding

[alrodopial]

place it just before the "end with"

[Captain Sensible]

Keeps throwing up errors alrodopial, dunno where I'm going wrong tbh

my sub code is just a few lines

Code: Select all

Sub data()


Dim qry As QueryTable
With Sheets("Sheet2")


Set qry = .QueryTables.Add(Connection:="URL;http://localhost/testing/excelgraph.php?ei=" & Sheets("Sheet1").Range("N12").Value & "&race=" & Sheets("Sheet1").Range("A10").Text, Destination:=.Range("A1"))
qry.Refresh


End With



Call bets
End Sub

Hoping to get a delay before the bets routine is called.

[alrodopial]

these may help

http://www.mrexcel.com/forum/excel-ques ... -load.html

http://stackoverflow.com/questions/8925 ... fresh-wait

usually the "with" is connected with the querry and not the sheet (like you did)

[Captain Sensible]

Thanks , I t've tweaked a version of this code and seems to be ok. Only time you find out for sure is when things go wrong I suppose

Code: Select all

Sub AddWebquery()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://de.selfhtml.org/html/tabellen/anzeige/table_tr_th_td.htm", _
        Destination:=Range("$A$1"))
        .Name = "table_tr_th_td"
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

I need to get some error trapping in there to handle long delays but so far seems fine.

I'm trying to make my sheets more efficient by using VBA and removing as many redundant formulas as possible just cos some only need to run at market change and things like my vlookups were pointless firing every refresh. One thing that dropped the cpu drastically was changing my lookups to the same sheet, maybe I'd referenced things inefficiently with formulas.

[mak]

i don't know if my thoughts are 100% correct, but the cpu problem has to do mainly with betfair. the same bots working ok for many months are now producing problems.

in a competitive to BA software there are complains regarding high cpu demands also and that can not be a coincidence.

that's why i am asking for the new ba version - in order to be efficient because right now it is not

[Captain Sensible]

Nah mine was certainly down to my poor coding, managed to get the sheet down to peaking at 10% compared to the 50% it used to do almost continually.

I'm not sure the API-NG is going to cure all your problems mak, it does look like it'll be more efficiently coded for Betfair but doesn't necessarily follow that it'll be more efficient for users and doubt they'll change the current API limits or servers.

The old API isn't due to be phased out until November and you don't see any of the other vendors clambering over themselves to implement it just yet. I'm sure it's still very buggy especially if you take into account Betfair's previous track record so don't expect to have a new version soon. I've got to recode some of my own php bots but can't see any benefits for me so I'll probably only look at it in September when any initial problems have hopefully been ironed out.

[mak]

as i said, i don't know what is the problem but betfair something has change in their code making BA consume much more cpu than before

i can't run the same bots in the same vps anymore.


for some reason BA and other software has become less efficient

[MiniBlueDragon]

Code: Select all

Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop

Mine's pretty similar to this.

Code: Select all

Set oDoc = oHTMLDoc.createDocumentFromUrl(strName, "")
Do While oDoc.readyState <> "complete": DoEvents: Loop