PDA

View Full Version : Automate a process to run every hour



rpatel121
02-06-2013, 04:40 AM
Excel 2010
VB for Apps 7.0

Dear all,

Im hoping someone could help me!

I have a Excel spreadhseat named - CommsMASTER.xlsm thats runs the following macro;



Sub ping08()
For Each tmpCell In Range("D2:1027").SpecialCells(2, 2)
If InStr(CreateObject("WScript.Shell").Exec("%comspec% /c Ping -4 -n 1 -w 750 " &
tmpCell).StdOut.ReadAll, "TTL=") Then
tmpCell.Offset(0, 3) = "OK"
Else
tmpCell.Offset(0, 3) = "Failed"
End If
Next
End Sub

This VB script basically pings a number of IP address and records the results as attached in spreadheet.

I would like to automate the process so the script runs automatically every hour and then records the results in each consequent/relevant cell. So next result at 9:00 in H2 and 10:00 in I2.

I have been looking at the Application.OnTime TimeValue("10:57:00"), "ping08" function but just cant anything to work as I would like.

As shown above, I have over 1000 rows to go through. I will have a dedicated server that this could run on.

Any help would be VERY MUCH APPRECAITED!