How to verify completion of file creation, from VBA

[kenmcd]

I need a way to monitor a file in a folder (created by an external application, such as Word or Acrobat), to verify that the process of writing to the file has completed (in Windows 2000 or XP). I.e., that the file is finished, and can be opened by Excel (or another app). It's easy to verify that the file exists, and what it's file length is, but the file length continues to increase as the file is written to (especially for a large file). Anyone know of a way in VBA to verify that the file is complete and can be read? I assume that Windows sets some kind of bit in the file to indicate completion. An error is generated if an attempt is made to read a file while it's still being written to. Thanks...

 

[WinblowsME]

Sub Get_Files_Folders()
   Dim start_dir As String, file_name As String, ret As Variant, curr_line As String, count As Long
   
   start_dir = "C:\"
   file_name = "C:\Documents and Settings\WinblowsME\Desktop\tempfile.txt"
   
   ret = Shell("Cmd.exe /c dir /b /s " & Chr(34) & start_dir & Chr(34) & " > " & _
                                         Chr(34) & file_name & Chr(34), vbMinimizedNoFocus)
   Call Wait_For_File_Completion(file_name)
   
   Open file_name For Input As #1
      Do While Not EOF(1)
         Line Input #1, curr_line
         count = count + 1
      Loop
   Close
   
   MsgBox "There are " & count & " lines in " & Chr(34) & file_name & Chr(34)
End Sub

Private Sub Wait_For_File_Completion(file_name As String)
   Dim log_len_prev As Long, log_len_curr As Long
   
   Do
      log_len_prev = FileLen(file_name)
      Pause (1)
      log_len_curr = FileLen(file_name)
   Loop While log_len_prev <> log_len_curr
End Sub

Public Sub Pause(seconds As Double)
   Dim start As Long
   
   start = Timer
   
   Do Until Timer - start >= seconds
      DoEvents
   Loop
End Sub

 

[kenmcd]

Thanks for the info - looks like it should work. I'll add it to my code. Thanks for your help!

Ken (Honeywell Aerospace)