Running cmd batch file repeatedly...

[JeffGrant]

Hi All, I know the answer is front of me, I just cant see it....

Because of time zone and data volume restrictions, I have to run a command line batch file, from VBA, in such as way that I can stay with the restrictions imposed by the API.
Essentially, the time that the batch file is run is pulled from a range within a worksheet.
On the first pass, the batch file is run 30 minutes before the time determined in the worksheet.
On subsequent passes the batch file is run 20 minutes before the time determine in the worksheet.
However, if the current data from the API is within the last 20 minute download then the current API download is missed because I already have the data.

Well that is the theory anyway.....

The initial Sub is called RunInitialAPITimer and that works fine,
RunInitialAPITimer calls RunAPITimer and that works fine.
The RunAPITmer calls WhenTimerRanCheck, so I can see what the timer ran. no drama.

On pass 3 and beyond, we get to this line:
If Now() > .Range("AE" & i) - TimeValue("00:30:00") Then,

because the condition is true on this iteration, it again runs the RunAPITimer Sub launching an immediate API download, BUT at the same time the Application.OnTime has been set from the previous iteration, and therefore the API download occurs twice within a minute of each other.

I am looking for some guidance/ideas on how to stop one of these itaerations.

As I said, I know the answer is front of me, I just can't see the forest for the trees on this one

Thank for your generous help and advice in advance.

Sub RunInitialAPITimer()
Dim TimeToRun As Date

With Sheet10
        TimeToRun = TimeValue(Format(.Range("AE2").Value, "hh:mm")) - TimeValue("00:30:00")
        Application.OnTime TimeToRun, "TimerUpdate"
        'start row = 2
        i = 2
        Call RunAPITimer
End With
End Sub


Sub RunAPITimer()

    With Sheet10
        If Now() > .Range("AE" & i) - TimeValue("00:30:00") Then
            Call TimerUpdate
            i = i + 1
            Call RunAppEvery20Minutes
            
        ElseIf Now() < .Range("AE" & i) Then
            Application.Wait (20)
        Else
            If Now() >= .Range("AE" & i) Then
                Exit Sub
            End If
        End If
    End With
End Sub
Sub RunAppEvery20Minutes()
    Application.OnTime Now() + TimeValue("00:20:0"), "RunAPITimer"
End Sub


Sub TimerUpdate()

Dim batchFilePath As String
batchFilePath = "C:\XXX\a.bat"

' Run the batch file and capture the process ID
Dim shellResult As Double
Dim shellProcess As Object
Set shellProcess = CreateObject("WScript.Shell")
shellResult = shellProcess.Run(batchFilePath, vbHide, True)


Call WhenTimerRanCheck
End Sub

Sub WhenTimerRanCheck()

    With Sheet10

        .Range("AF" & i + 28).Value = "Timer ran at:"
        .Range("AH" & i + 28).Value = Now()

    End With
End Sub

 

[offthelip]

The solution to my way of thinking is to avoid using "Application.OnTime TimeToRun, "TimerUpdate"" because this triggers the update. the sub you should be using with the ontime trigger is the sub that checks whether it is time to do an update, This problem is very similar to a problem in this thread
Auto close spreadsheet timer not working correctly