xmlHTTP optimize code

[iknowu99]

Aloha everybody,

I am runing the following code yet it doesnt seem the best way to do it. The goal is to get data from online everytime the time stamp changes.

'excel2007

Sub macro()


Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
strURL = "http://www.yahoo.com" 'actually another website that has a timestamp and goes to A1
xmlhttp.Open "GET", strURL, True, "", "" '
Call xmlhttp.Send

' Lets set a timeout of 3 seconds
timeout = Timer + 3
While xmlhttp.readyState <> 4 And timeout > Timer
    ' Lets prevent the script from feezing
    DoEvents
Wend

' Is the call okay - 200 is the HTTP response code for OK but it does not work for me so commented out
If xmlhttp.readyState = 4 Then  'And xmlhttp.Status = 200 Then
    RtnPage = xmlhttp.responseText
Else
    nothingNew = True
    GoTo endSUB
End If


Set fs = CreateObject("Scripting.FileSystemObject")
Set ah = fs.CreateTextFile("C:\default.html", True)
ah.Writeline (RtnPage)
ah.Close

'there is a query to refer to default.html that just got saved to C:\
Sheets("Sheet1").QueryTables(1).Refresh BackgroundQuery:=False
'it does not reference online directly because there were some problems with updating so often from online query so i used the html way. still cant find a clear direction as to how to utilize the refresh with under every minute run...and it seems to me xmlHTTP way is faster so i prefer it


GeneratedA1 = Sheets("Sheet1").Range("A1")

If GeneratedA1 = "" Then
    nothingNew = True
    GoTo endSUB
End If


'everytime A1 in Sheet1 should be updated as the time the data is received, this happens on the original website, so if new time stamp is created online then the sub creates "nothingNew = false"

With Sheets("Tracking")
LastRowTrack = .Cells(65536, "A").End(xlUp).Row + 1
    If .Cells(LastRowTrack - 1, 1) = GeneratedA1 Then
        nothingNew = True
        GoTo endSUB
    End If
    .Cells(LastRowTrack, 1) = GeneratedA1
    .Cells(LastRowTrack, 2) = Now
End With



endSUB:



End Sub

 

[iknowu99]

the site holds data statistics. if the timestamp, which is the first thing to show up on the site, changes that means the statistcs are updated. i'm trying to keep track of these statistics....sort of like getting data from yahoo stocks when the share value changes....except in my case the time stamp reflects changes.

when this change occurs i want the specific html file to be entered into excel sheet. it doesnt seem there will need to be special formating since just updating the query creates this effect. but for me query does not work well enough since there might be problems when there is no connection or other problems....internet query freezes excel often since i have a timer to run it very often....that's why i went to XMLHTTPrequest, it seems more effective and there are ways to continuosly run the code even if there are errors (bad connection or whatever). it is important that this vba runs on its own....often it freezes with query and xml is not working for me either. so ...i'm still searching for a full proof solution. thank you.

 

[xenou]

So, just to clarify, do you 1) open excel and run a macro continuously? or 2) use windows scheduler to execute the program at set intervals? And, when you say you run this frequently, do you mean every 20 seconds? 10 seconds? 2 seconds?

It's not my strength - in fact, its a decided weakness - to use vba for timed or continuous execution, so I'm not sure I'll be of much help (anything I get around to will probably be much too late). I can't help but think that this would be a better job for a smaller application that can run in the background, and simply strip the info you need from the page source and save it to a file (although, its hard to get down to that without access to the page - something along the lines of an XPath query or DOM expression, or even just a simple string operation on the html source).

There are by the way, website monitoring software programs available. The most interesting one (for free) I could find seems to be able to email you the website when a change occurs - Outposted and URLy Warning, from the list below. No idea as to the real value or utility of these, or how well they would work at the rate you need. Most of these programs seem geared to simply alert you of changes rather than actually capture them.
http://www.topshareware.com/web-page-change-monitor/downloads/1.htm

Sorry - I'm not much help here.

 

[ZVI]

Hi,

To help you find date-time stamp element of the site page, its HTML source code is required.
That is, the text contents of RtnPage variable or (the same) text contents (source code) of C:\default.html file is required with information about of what table of C:\default.html file is accessed by the QueryTable.

But without this info may be it’s enough to calculate CRC32 of RtnPage string variable, assuming that changing of the site page contents means the changing of date-time stamp as well.
In this case changing of CRC32 is the flag of html code changing.

For cycling the method Application.OnTime can be used, you can find numerous examples of this method implementation on the Board.

Regards,
Vladimir