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]

bump

somebody's gota be working with online data / online queries, where are the gurus?

 

[iknowu99]

bump

 

[xenou]

I'm in over my head but my take is:
1) its not clear how you plan to use this routine. If you want to check the web page for changes, will you be running this code continuously day and night?
2) is it possible to strip out the information desired right from the html returned by the xlmhttp response text

xenou

 

[iknowu99]

Hi xenou,
Thanks for the response

1. Yes day and night, continuously monitor the timestamp
2. it would not make sense to strip data when html pasted special, or even saved as xls file will create the organized data i'm looking for

 

[xenou]

Hmmm, that interesting. So do you have this code scheduled to run every so often via Windows scheduler? Could you run a vbs script instead without involving Excel? It seems to be what you need - I'm just feeling like there has to be some way to check the timestamp for changes without having to open Excel and run a query - that is, to use Excel after (and only if) a change is detected. Its like one of those odd things programs are always doing - sending out http requests in the background to check for updates!

 

[iknowu99]

yep this is what i want,

i have limited knowledge about vb script. maybe you can tell me how do you run vb script? and compiler?

 

[xenou]

One thing I can say about wscript - it's awfully hard debugging it! I think there's a debugger out there somewhere - anyway, below is a try. You really just save the code as is, with a .vbs extension, then "open" or "run" the file.

From my experiences:
1) don't use Sub/End Sub
2) don't Dim variables

Now that I think about it, given the troubles I've had debugging wscript, I'm recalling my suggestion! But if you can get it to work...it could be a neat little solution. Below is only a recreation of your code in wscript, with the difference that it "passes" execution to Excel at the point where Excel is opened (Excel runs its own macro then). Not really checking the URL though. I can't really say I think its performing any better than Excel either! Not at this point, anyway. I've never tried any string comparison with wscript - I have to assume its possible but really not sure.

This file is available as a download <a href="http://northernocean.net/etc/mrexcel/20091031_checkURL.zip">here</a>

set objWSH = CreateObject("WScript.Shell") 
objWSH.Popup "Start"

strSaveHTMLFilePath = "C:\myTemp\default.html"
strExcelWorkbookPath = "C:\myTemp\test.xlsm"
strURL = "http://www.google.com"

Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
xmlhttp.Open "GET", strURL, True, "", ""
Call xmlhttp.Send

objWSH.Popup "Sent"

timeout = Timer + 3
Do Until xmlhttp.readyState = 4
    If timeout > Timer Then
		WScript.Quit
	End if
Loop

objWSH.Popup "Returned XMLHTTP"

Set fs = CreateObject("Scripting.FileSystemObject")
Set ah = fs.CreateTextFile (strSaveHTMLFilePath, True)
ah.Writeline xmlhttp.responseText
ah.Close

Set XL = CreateObject("Excel.Application")

objWSH.Popup "Make Excel visible"

With XL
	.Visible = True
	Set wb = .Workbooks.Open(strExcelWorkbookPath, False, False)
	objWSH.Popup "Opened Workbook"
	.Run (wb.Name & "!Macro1")
End With

On Error Resume Next
wb.Close True
XL.Quit

 

[iknowu99]

sorry this solution is not working for me. i'm gona try to stick with vba, anybody new solutions?

 

[xenou]

If the requirement is that code run from Excel, I can't think of any way to improve it - it seems your code requires Excel to run the query and return it to a cell on a worksheet, with further calculations or operations operating on that cell.

I'm still rather stuck on the idea that it should be possible to "check" the site for changes without involving Excel - then act accordingly if changes are found. But its hard to say without knowing more about the website and what changes you are looking for in the html source, and what is done with it after that. Minimally, I would think you could separate the first part from the rest (checking for changes).

ξ