CODEWITHSUNDEEP
excelvba
monish kumar k
monish kumar k

Reputation: 29

How to Run Excel VBA Macros only for sometime

I have developed a excel macro which checks the particular directory for a File, if there is a matching file it will pick and execute the macro, my point is I want to check the directory only for 5mins if the file is created/appears in the mentioned directory it should pick and process, if not throw an error. How to run macro only for a particular time.

Do

    If fso.FileExists("file") Then

        Exit Do
    End If

    DoEvents 'Prevents Excel from being unresponsive
    Application.Wait Now + TimeValue("0:00:01") 'wait for one second
Loop

MsgBox "file available"

Upvotes: 0

Views: 198

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Try this code, please. It wait 10 seconds. It is easy to be adapted at what you need.

Sub testFiniteLoop()
 Dim start As Date, fso As Object, fileName As String, boolFound As Boolean

 Set fso = CreateObject("Scripting.FileSystemObject")
 fileName = "C:\test.txt" ' use here your full file name
 start = Now
 Do While Now <= start + TimeValue("00:00:10")' use here what time you need

    If fso.FileExists(fileName) Then
        boolFound = True
        Exit Do
    End If

    DoEvents 'Prevents Excel from being unresponsive
    Application.Wait Now + TimeValue("0:00:01") 'wait for one second
 Loop

 MsgBox IIf(boolFound, "File available", "Checking time passed")
End Sub

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Here is a typical way to wait for an event for a limited period of time:

Sub DualCriteria()
    Dim tStart As Date, tNow As Date
    msg = ""
    tStart = Now

    Do
        DoEvents
        If Range("A1").Value <> "" Then
            msg = "event occured"
            Exit Do
        End If
        tNow = Now
        If tNow - tStart > TimeSerial(0, 5, 0) Then
            msg = "timeout"
            Exit Do
        End If
    Loop

  MsgBox msg
End Sub

The macro waits for the User to put a value in cell A1. If nothing happens for 5 minutes the loop exits anyway.

A message tells you why the loop ended.

NOTE:

The Doevents allows focus to be shared with worksheet activities.

Upvotes: 2

Related Questions