Function to wait until a particular file exists

[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