AutoTrigger

[vmjan02]

I have this script and is only working if I do debug (Alt+f8), but if i run the report it is not doing the doevent part, no error at all.
Any help as what is wrong.

Ref the screen shot for time to auto trigger, or to do Doevent for the condition match's scheduledTimes = timeValue(now)

Sub RunFileAtScheduledTime()
    Dim filePath As String
    Dim scheduledTimes(1 To 3) As Date ' Array to store scheduled times
    Dim otherWorkbook As Workbook
    Dim i As Integer

    ' Set the path to the other workbook
    filePath = "C:\Viral Shah\Automation\Hourly Pending Tickets\Hourly Pending Tickets V2.xlsm"

    ' Read the scheduled times from cells C3, D3, and E3 of the "RunReportTime" sheet
    scheduledTimes(1) = Sheets("RunReportTime").Range("C3").value
    scheduledTimes(2) = Sheets("RunReportTime").Range("D3").value
    scheduledTimes(3) = Sheets("RunReportTime").Range("E3").value

    ' Loop indefinitely
    Do
        ' Loop through scheduled times
        i = 1 ' Initialize i to start from the first scheduled time
        Do While i <= 3
            ' Check if the current scheduled time has passed
            If scheduledTimes(i) < timeValue(Now) Then
                ' Move to the next scheduled time if the current one has passed
                i = i + 1
            Else
                ' Wait until current scheduled time matches the current time
                Do Until scheduledTimes(i) = timeValue(Now)
                    DoEvents
                Loop
                
                ' Open the other workbook
                Set otherWorkbook = Workbooks.Open(filePath)

                ' Call the allrun subroutine from the other workbook
                Application.Run "'" & otherWorkbook.Name & "'!allrun"

                ' Close the other workbook
                otherWorkbook.Close SaveChanges:=True

                ' Exit the loop to start over the outer loop
                Exit Do
            End If
  
        Loop
    Loop
End Sub

[ATTACH type="full"]106511[/ATTACH]

 

[CephasOz]

Have you thought about splitting your procedure into two, and using Application.OnTime? Coded this way, it wouldn't even matter if the times weren't in order.

Sub ExecuteOnTime()
    Const clngRow As Long = 3
    Dim lngCol As Long
    Dim datToStart As Date
    ' Read the scheduled times from cells C3, D3, and E3 of the "RunReportTime" sheet
    For lngCol = 3 To 5
        datToStart = Sheets("RunReportTime").Cells(clngRow, lngCol).Value
        Application.OnTime datToStart, "RunReport", datToStart + TimeValue("00:00:30")
    Next
End Sub

Sub RunReport()
    Dim otherWorkbook As Workbook
    Const filePath As String = "C:\Viral Shah\Automation\Hourly Pending Tickets\Hourly Pending Tickets V2.xlsm"
    Set otherWorkbook = Workbooks.Open(filePath)

    ' Call the allrun subroutine from the other workbook
    Application.Run "'" & otherWorkbook.Name & "'!allrun"

    ' Close the other workbook
    otherWorkbook.Close SaveChanges:=True
End Sub