Hi, All
I am working on a excel - attch, which has a macro helping me pull in the 'local time' ( Column M ) of different countries from an Internet Server.
I need help in modifying the below code. to schedule the macro run every hour,
Kindly advise. Looking forward for your expert help.
Sub excelforum()
Application.ScreenUpdating = False
Dim TaD As MSXML2.XMLHTTP, cell As Range
Dim url$, city$, x&
On Error GoTo earlyexit
Set TaD = New MSXML2.XMLHTTP
url = "http://www.thetimenow.com/suggest.php?location="
For Each cell In Range("L3:L" & Cells(Rows.Count, "L").End(xlUp).Row)
Application.ScreenUpdating = True
cell.Activate
DoEvents
Application.ScreenUpdating = False
city = Replace(LCase(Cells(cell.Row, 12)), " ", "+")
x = 0
TaD.Open "GET", url & city & "&" & CInt(1000 * Rnd()), False
TaD.send
If TaD.responseText = "[]" Then cell.Offset(0, 1) = "Couldn't find the time, sorry!": GoTo nxc:
If Not cell.Offset(0, -1) = vbNullString And Not cell.Offset(0, -1) = "Unknown" Then x = InStr(TaD.responseText, cell.Offset(0, -1))
If x > 0 Then cell.Offset(0, 1) = Mid(TaD.responseText, InStr(x, TaD.responseText, Chr(34) & "," & Chr(34)) + 3, 5): GoTo nxc
x = InStr(TaD.responseText, cell.Offset(0, -2)) - 8
If x > 0 Then cell.Offset(0, 1) = Mid(TaD.responseText, x, 5): GoTo nxc
cell.Offset(0, 1) = "Couldn't find the time, sorry!"
nxc:
Next cell
earlyexit:
Application.ScreenUpdating = True
End Sub
Bookmarks