[SOLVED:] Function to wait until a particular file exists

Function to wait until a particular file exists

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

Code:

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.

Perhaps something like:

Code:

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

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

Code:

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

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... :thumb

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.

Code:

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:

Code:

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! :beerchug:

Cheers,

G.T.

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.

:dunno

Consider it done.

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.

A few things don't show with Chrome.