Function to wait until a particular file exists

[GreenTree]

Hello,

I'd like to have a function which will wait until a particular file exists, or until a specified time passes. Something along the lines of

Function WaitForFile (FilePathAndName as String, TimeOutSeconds as Integer) as Boolean

The idea being, the function will return True if the file passed as the first parameter appears before the time expires, or False if the file does not show up before the number of seconds given in the second parameter has passed. I've got the FileExists() function just fine (thanks to the Knowledge Base here), but getting the timing part down (without running the CPU usage up to 100% & checking the disk continuously) has me puzzled. If the program could check for the file every 10 seconds or so, and use minimal CPU cycles in between checks, that would be ideal.

Any thoughts?

Thanks,

G.T.

[mikerickson]

Perhaps something like:

Function WaitForFile(filePathAndName As String, timeOutSeconds As Long) As Boolean
    Dim Begin As Long
    Begin = Timer
    On Error Resume Next
    Workbooks.Open filePathAndName
    WaitForFile = ((CLng(Timer) - CLng(Begin)) < timeOutSeconds) And (Err = 0)
    On Error GoTo 0
End Function

[Paul_Hossler]

You could use OnTime. It's the only way I know to not just sit there burning CPU cycles. Modify it to only check once, or test for it to come back with a result

I'm not sure it's what you're looking for, but might give you some ideas

Option Explicit

Enum CheckingStatus
    xKeepLooking = 1
    xCanceled = 2
    xFound = 3
End Enum
 
Public KeepLooking As CheckingStatus
Public FileToLookFor As String
Public SecondsPerLoop As Long
 
Sub StartLooking()
    KeepLooking = xKeepLooking
    FileToLookFor = "C:\TheFile.txt"
    SecondsPerLoop = 2
    Call CheckForFile
End Sub
 
Sub StopLooking()
    KeepLooking = xCanceled
End Sub
 
Sub CheckForFile()
    If Dir(FileToLookFor) <> "" Then
        KeepLooking = xFound
        MsgBox "File Found"
    ElseIf KeepLooking = xKeepLooking Then
        Call Application.OnTime((Now + SecondsPerLoop / 86400), "CheckForFile")
    ElseIf KeepLooking = xCanceled Then
        MsgBox "Canceled by User"
    End If
End Sub

Paul

[GreenTree]

Mike, thanks for posting. I think that solution will work, but it will drive the CPU utilization up to 100%, and I was hoping to avoid that, especially since some of what I have in mind may involve some lengthy waits while other programs are running on the same machine (i.e. the one that will hopefully produce the file that VBA is waiting for).

Paul's solution gets a lot closer, since it does not use any CPU cycles while it waits, but it doesn't work as a procedure call... I can't return control to the same place in the program that I called it from once the file has been found or the time-out has been reached. If I were going to call this from only one place, that would work, but needing to call it from multiple places in the code makes this a tough solution. It did get me started, though...

Where I ended up was putting up a MODAL (form properties, Show Modal = True -- which is the default) user form, since control DOES return to the same place you left off when the user form is eventually dismissed. That way I was able to put everything inside a function, shown below. I built in the possibility of looking for 3 different files & returning a 1, 2, or 3 to indicate whichever one shows up first, along with passing the relevant parameters to the function so it's reasonably general. A 0 return indicates the search timed out with nothing found, and a -1 indicates that the user cancelled the search before the timeout and before anything was found.

This code calls the ever-helpful function FileOrDirectoryExists, from the Knowledge Base.

Option Explicit

Public Enum WaitForStatusEnum
    File1Found = 1
    File2Found = 2
    File3Found = 3
    StillWaiting = 99
    TimedOut = 0
    UserCnx = -1
End Enum

Public WaitFor_File1 As String
Public WaitFor_File2 As String
Public WaitFor_File3 As String
Public WaitFor_Interval As Integer     ' # of seconds between checks
Public WaitFor_Deadline As Date        ' calculated in the function based on parameters passed
Public WaitFor_NextCheck As Date     ' if you want to cancel a future check, its scheduled time is here
Public WaitFor_Status As WaitForStatusEnum

Function WaitForFiles(File1 As String, File2 As String, file3 As String, TimeoutHours As Integer, TimeoutMinutes As Integer, _
    TimeoutSeconds As Integer, CheckIntervalSeconds As Integer) As Integer
    ' Function accepts three file names (full path) (any or all may be empty) and a timeout expressed as a number of
    ' hours/minutes/seconds in the future, and a check interval (i.e. how often checks will be made) expressed as a
    ' number of seconds.  While the checks are being made, the form frmWaitForFiles is displayed and the wait can be
    ' canceled by the user from there.
    '
    ' The function returns an integer: 1, 2, or 3 correspond to File1 or 2 or 3 was found (only the first one to be
    ' found will be reported); 0 indicates the wait timed out before any files were found, and -1 indicates the wait
    ' was cancelled by the user.

    ' put the passed parameters into the Public variables
    WaitFor_File1 = File1
    WaitFor_File2 = File2
    WaitFor_File3 = file3
    WaitFor_Interval = CheckIntervalSeconds
    WaitFor_Deadline = Now + TimeSerial(TimeoutHours, TimeoutMinutes, TimeoutSeconds)
    frmWaitForFiles.TextBox1.Text = "Excel (VBA) is waiting until " & WaitFor_Deadline & " or until one of the following files is found:" _
    & vbCrLf & File1 & vbCrLf & File2 & vbCrLf & file3 & vbCrLf _
    & "Check interval = " & CheckIntervalSeconds & " seconds.  You can click CANCEL to stop waiting." _
    & vbCrLf & "Each 'c' represents one check: "
    WaitFor_Status = StillWaiting
    WaitFor_NextCheck = Now + 1 / 86400        ' First check will happen one second from now; all subsequent checks happen at intervals
    Application.OnTime WaitFor_NextCheck, "WaitFor_CheckNow"
    frmWaitForFiles.Show
    ' Because frmWaitForFiles is MODAL, execution here pauses until frmWaitForFiles is dismissed (one way or another)
    ' By that time, we expect WaitFor_Status to be resolved into something BESIDES StillWaiting.
    Select Case WaitFor_Status
        Case Is = File1Found
            WaitForFiles = 1
        Case Is = File2Found
            WaitForFiles = 2
        Case Is = File3Found
            WaitForFiles = 3
        Case Is = TimedOut
            WaitForFiles = 0
        Case Is = UserCnx
            WaitForFiles = -1
        Case Is = StillWaiting
            ' Don't expect to ever get here
            WaitForFiles = 99
            MsgBox "Unexpected result: WaitForFiles got back a 'Still Waiting' status!"
        Case Else
            ' Don't expect to ever get here
            WaitForFiles = 100
            MsgBox "Unexpected result: WaitForFiles got back an unknown status!"
    End Select
End Function


Sub WaitFor_CheckNow()
    ' Check if any of the files exists, and if none then check if timed out
    If FileOrDirExists(WaitFor_File1) Then
        WaitFor_Status = File1Found
        frmWaitForFiles.Hide    ' which will return control to the function above immediately after the .show command
        Exit Sub
    ElseIf FileOrDirExists(WaitFor_File2) Then
        WaitFor_Status = File2Found
        frmWaitForFiles.Hide    ' likewise
        Exit Sub
    ElseIf FileOrDirExists(WaitFor_File3) Then
        WaitFor_Status = File3Found
        frmWaitForFiles.Hide
        Exit Sub
    ElseIf Now > WaitFor_Deadline Then
        WaitFor_Status = TimedOut
        frmWaitForFiles.Hide
        Exit Sub
    ElseIf WaitFor_Status = UserCnx Or WaitFor_Status = TimedOut Then
        frmWaitForFiles.Hide    ' if we somehow get here with this status, hide the form & scram
        Exit Sub
    End If
    frmWaitForFiles.TextBox1.Text = frmWaitForFiles.TextBox1.Text & " c"    ' add a marker to the textbox to show activity over time
    ' Nothing has been found and we're not timed out, so schedule the next check
    WaitFor_NextCheck = Now + WaitFor_Interval / 86400
    Application.OnTime WaitFor_NextCheck, "WaitFor_CheckNow"
End Sub

' Simple code to put all this to a test:
Sub WaitForFilesTest()
    Dim TempResult As Integer
    MsgBox "Wait For Files Test begins."
    TempResult = WaitForFiles("c:\test1.txt", "c:\test2.txt", "", 0, 1, 0, 5)
    ' This will look for the 2 named files, checking every 5 seconds, timing out
    ' after one minute if nothing is found by then.
    MsgBox "Wait For Files Test ends.  Returned value = " & TempResult
End Sub

The form frmWaitForFiles has TextBox1 that simply displays a message onscreen describing what's going on, with an ever-expanding list of "c c c c" so that you can see as new checks are, in fact, being made. It also has a cancel button, btnUserCancel, and a "Check Now" button, btnCheckNow, which is probably a bit extraneous (and will introduce an extra check in between the ones occurring at the specified interval. The code for this form is:

Private Sub btnCheckNow_Click()
    WaitFor_CheckNow
End Sub

Private Sub btnUserCancel_Click()
    WaitFor_Status = UserCnx
    ' Hide the form, returning control to the WaitForFiles function
    Me.Hide
End Sub

With the test sub, I start with a text file in the C:\ directory named test.txt (i.e. NOT the file being sought), let a few checks run, then rename it to test1.txt or test2.txt, and observe that the Test does return the correct "1" or "2". It will also time out after a minute (returning a "0" code), and reports accurately when the user at the console tells the program to stop looking.

Hope this may be useful to someone.

Thanks again to Mike & Paul for engaging on the thread!

Cheers,

G.T.

[GreenTree]

One other thing, I can't seem to find where to mark the thread as solved... all I have for Thread Tools are printable version, email, and unsubscribe. I'd like to mark the thread as solved, but can't seem to find the right control to do so.

[Aussiebear]

Consider it done.

[GreenTree]

Thanks, Aussiebear! May have been something with Chrome, because the tool to mark the thread as solved showed up exactly where I expected it when I came back using Firefox.

Cheers!

G.T.

[mdmackillop]

A few things don't show with Chrome.