How to tell if Windows text file is closed

Topic

New Reply

I have a situation where two computers (A and B) are on a LAN and access a shared folder. This is all working fine. Our Access app is interfacing with a POS system that writes a text file in a particular folder. We pick up this file and process it. 99.9% of the time this works without a problem.

Occasionally, we think we have a situation where we are trying to open the text file before it is fully closed by the POS app. Our code goes something like (pseudo code follows):

findfile = dir(full-path-name)
if findfile “” then
sleep (2000)
open text file
process text file
end if

The sleep command was put in specifically to allow sufficient time for the file to be closed by the other app before we open it. We think, however, that we need a more foolproof way of detecting of the file is fully closed (or, if as we suspect, it only has an entry in the Windows FAT).

Can anyone suggest some VBA code to detect if a text file is really closed?

Thanks.

Reply | Quote

Replies

You might try opening the text file for output first. As long as the POS application is writing the file, that should not be possible, so you should get an error that you can intercept using an error handler.
If you succeed, close the file, then reopen it in the mode you want.

Reply | Quote

Hans,

Thanks for the reply.

I had thought of this solution but we have some concerns about opening “their” file for output, even with error checking.

What about the idea of checking the file size using the LOF function after we open it for input? The text files are all 1 KB in size. So, if a zero is returned from the LOF function, do you think that would show that the file isn’t yet closed by the POS app?

Reply | Quote

Whether the file size will be 0 depends on the way the POS application writes the file – in one chunk or in smaller steps. But it’s worth a try.

You can check the file size before opening the file, by using the FileLen function:

Code:

Dim strFile As String
Dim lngFileSize As Long
strFile = "..."
Do
  DoEvents
  lngFileSize = FileLen(strFile)
Loop Until lngFileSize > 0
Open strFile For Input As #1
...

Reply | Quote

Hi Carol you could check if it is open using code similar to this:

Code:

Sub TestIfOpen()
 Dim strFileName As String
 
 strFileName = "C:test.txt"
 
 If Not FileLocked(strFileName) Then
 	' If the function returns False, open the document.
 	Open strFileName For Random As #1
 End If
 'Do some stuff here otherwise
 'Close
 
End Sub

in conjuction with

Code:

Function FileLocked(strFileName As String) As Boolean
 On Error Resume Next
 
 Open strFileName For Binary Access Read Write Lock Read Write As #1
 Close #1
 
 If Err.Number  0 Then
 	
 	MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
 	FileLocked = True
 	Err.Clear
 End If
End Function

Reply | Quote

Thanks, Jerry.

I’ll give it a try!

Reply | Quote

I had a similar issue and found the best method was to rename the file after writing had completed. This only works properly if the writing application performs the rename.

cheers, Paul

Reply | Quote

Thanks to all for your replies.

Upon reconsideration, I have decided to wait until I can speak with the POS app developers (or as close as I can get to them) before I make any changes in my app. Unfortunately, they are on “break” this week so I’ll have to wait until next week to get further insight into how they write their file.

Happy New Year to all!

Reply | Quote