Solved: 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

[VBA]Function WaitForFile (FilePathAndName as String, TimeOutSeconds as Integer) as Boolean[/VBA]

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:

[VBA]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
[/VBA]

[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

[vba]
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
[/vba]

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.

[VBA]
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
[/VBA]

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:

[VBA]
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
[/VBA]

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.