Waiting for file to create before reading

[adbrad]

For a single macro, I want to create a file in an outside program, wait for it to create, open it to read it. I can use Wait or Sleep while checking if the file exists. I was looking for code to check for the file, but wait until it's completely created to open it. Would trying to open it while it's still creating show an error or does FileExists returns true if the file is still being created?

I tried googling and looking through this forum, but can't find correct answer or can't figure out key words in the search.

 

[Jeffrey Mahoney]

Please try this:

Run the LoopFileExist to start. Edit the file name in the NextCheckFileExist SUB as needed

Public BegTime As Date
Public CheckCount As Long

Public Function FileExist(PFN As String) As Boolean
  Dim TempFN As String
  TempFN = Dir(PFN, vbNormal)
  If TempFN <> "" Then FileExist = True
  
End Function


Sub LoopFileExist()

  BegTime = Now() + TimeValue("00:00:5")
  CheckCount = 0
  Application.OnTime BegTime, "NextCheckFileExist", Schedule:=True   'Schedule NextTime
  
End Sub


Sub NextCheckFileExist()
  If FileExist("C:\Temp\Jeff.txt") Then
    MsgBox "Your file is ready to read"
  Else
    CheckCount = CheckCount + 1
    Debug.Print CheckCount
    If CheckCount > 10 Then Exit Sub
    BegTime = Now() + TimeValue("00:00:5")
    Application.OnTime BegTime, "NextCheckFileExist", Schedule:=True   'Schedule NextTime
  End If
  
End Sub

 

[adbrad]

Thanks for your quick reply. Would this work if the file was being written to (0 bytes) at the time of FileExists check? Or could you use IsFileOpen to check that?

 

[Jeffrey Mahoney]

This will tell that a file exists. Here is some code I have to determine if the file is open by another App or person. Some text editors don't lock the file so this returns false.

Function FileLocked(strFilename As String) As Boolean
   On Error Resume Next
   ' If the file is already opened by another process,
   ' and the specified type of access is not allowed,
   ' the Open operation fails and an error occurs.
   Open strFilename For Binary Access Read Write Lock Read Write As #1
   Close #1
   ' If an error occurs, the document is currently open.
   If Err.Number <> 0 Then
      ' Display the error number and description.
      MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
      FileLocked = True
      Err.Clear
   End If
End Function