VBA web services

[iknowu99]

Hello All,

I am using the same macro i've been using for awhile but now i'm getting something new!

new error/mistake that is skipping the code i want to run, it does not retrieve data from online although it should.

XMLHttp.readyState equals one during run time so it goes on to next process, after searching it seems like when it equals one it means that it is processing?? i dont know

and yeaa.... any code improvement would be great, this works but i know it's not proper to have a query go to C:\ and retrieve the file that was saved from XML back to Sheet1....only how is proper?!?

'excel2007

Sub macro()


Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
strURL = "http://www.yahoo.com" 'actually another website that has a timestamp and when retrieved goes to Sheet1 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
If xmlhttp.readyState = 4 Then  'And xmlhttp.Status = 200 Then 'here its going to else because readystate=1
    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

Sheets("Sheet1").QueryTables(1).Refresh BackgroundQuery:=False



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

o yea and this runs on a timer that runs every minute to check if timestamp is new online....

 

[iknowu99]

bump*

i have limited knowledge about ODBC but would that be relevant here?

 

[iknowu99]

okay so i think i accidentally stumbled upon what the hell was wrong before

Before i enabled all macros to run on my pc. Now i tried the option like the expert Tushar suggested where each time it asks me to run the macro or not...and the trigger for run timer even is coded in workbook_open function

Private Sub Workbook_Open()
    StartTimer
End Sub

ok so this will let the macro live again but it doesnt seem so efficient. maybe i will repost to get some help optimizing it; any suggestions?