VBA/Excel/Access/Word/Application/Application OnTime

Материал из VB Эксперт
Перейти к: навигация, поиск

If you want to keep refreshing the data on a regular basis, you can make the macro run itself

     Dim mdteScheduledTime As Date
     Sub RefreshData()
         ThisWorkbook.UpdateLink Name:="C:\YourExcel2007File.xlsx", Type:= xlExcelLinks
         mdteScheduledTime = Now + TimeSerial(0, 1, 0)
         Application.OnTime mdteScheduledTime, "RefreshData"
     End Sub
     Sub StopRefresh()
         Application.OnTime mdteScheduledTime, "RefreshData",, False
     End Sub

Run on time

Sub RunOnTime()
   Application.OnTime Date + TimeSerial(15, 0, 0), "RefreshData"
End Sub

Scheduling a Macro to Run Every Two Minutes

Sub ScheduleAnything()
    WaitHours = 0
    WaitMin = 2
    WaitSec = 0
    NameOfThisProcedure = "ScheduleAnything"
    NameOfScheduledProc = "RemindMe"
    NextTime = Time + TimeSerial(WaitHours, WaitMin, WaitSec)
    Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfThisProcedure
    Application.Run NameOfScheduledProc
End Sub
Sub RemindMe()
    Application.Speech.Speak Text:="meeting."
End Sub

Set Refresh Data Timer

Sub RunOnTime()
   Application.OnTime (Date + TimeSerial(10, 47, 0)), "RefreshData1"
End Sub

The OnTime event

Sub SetAlarm()
    Application.OnTime 0.625, "DisplayAlarm"
End Sub
Sub DisplayAlarm()
    MsgBox "Wake up. It"s time for your afternoon break!"
End Sub

Use OnTime to trigger the refresh data

     Sub RunOnTime()
         Application.OnTime Date + TimeSerial(15, 0, 0), "RefreshData"
     End Sub
     Sub RefreshData()
         ThisWorkbook.UpdateLink Name:="C:\YourExcel2007File.xlsx", Type:=xlExcelLinks
     End Sub

Using Application.OnTime to Periodically Analyze Data

Sub ScheduleTheDay()
    Application.OnTime EarliestTime:=TimeValue("8:00 AM"), Procedure:=CaptureData
    Application.OnTime EarliestTime:=TimeValue("9:00 AM"), Procedure:=CaptureData
End Sub
Sub CaptureData()
    Dim myWorksheet As Worksheet
    Dim NextRow As Long
    Set myWorksheet = Worksheets("Sheet1")
    myWorksheet.range("A2").QueryTable.Refresh BackgroundQuery:=False
    Application.Wait (Now + timeValue("0:00:10"))
    NextRow = myWorksheet.cells(65536, 1).End(xlUp).row + 1
    myWorksheet.range("A2:B2").copy myWorksheet.cells(NextRow, 1)
End Sub

writes the time to cell A1 and also programs another event five seconds later.

Dim NextTick As Date
Sub UpdateClock()
    ThisWorkbook.Sheets(1).Range("A1") = Time
    NextTick = Now + TimeValue("00:00:05")
    Application.OnTime NextTick, "UpdateClock"
End Sub
Sub StopClock()
    On Error Resume Next
    Application.OnTime NextTick, "UpdateClock", , False
End Sub