Detremine if text file open

[SkyCoder]

Hi All

How do I determine the status of a text file. Ie is the file open or not. (the text file is bieng used in a mail merge and i get an error when trying to overrite it)

[Keithuk]

If your trying to open a file thats already open then you will get a error 55 - File already open. You must decide what you want to do. Close will shutdown all open files in your app or Close #1 (1 = file number allocated). You will have to sort your own file numbers.

Make a function to open the specific file, if theres an error then Close it again so you can use it. It should return a Boolean, True if already open.

[Buzby]

You could just catch the error and process it - in other words, do

On Error Go To FileInUse

Open "thisfile" For Output as #1
..
..
..



FileInUse:
Msgbox "The file is in use - please try again later."

[SkyCoder]

Thanks Buzby but

the section "On Error Go To FileInUse" is highlited in red if I do that. Does anything have to be declared or what

[crptcblade]

Go To should be GoTo.

[SkyCoder]

Can i use this "On error" in a function aswell

[SkyCoder]

Public Function rs_letter(ByRef rsSource As ADODB.Recordset, WbrId As String) As Integer
Dim lFld As Long
Dim lRow As Long
Dim LogFileNumber, x, x1, BS As Integer
Dim LogFileName, sFile, sFields, sVals As String
On Error GoTo Err

If Not rsSource Is Nothing Then
sFile = "LetterData"
LogFileNumber = FreeFile ' Get unused file number.
LogFileName = App.path & "\Correspondence\" & sFile & ".txt"
' On Error GoTo FileInUse
Open LogFileName For Output As LogFileNumber ' Create file for append.

rsSource.MoveFirst
If rsSource.Status = adStateClosed Then
sFields = "Current Date|"
sVals = sVals & Format(Date, "YYYY/MM/DD") & "|"
rsSource.MoveNext
For x1 = 0 To 1
For x = 1 To 10
If x1 = 0 Then
sFields = sFields & "Seller " & rsSource.Fields(x).Name & "|"
Else
sFields = sFields & "Buyer " & rsSource.Fields(x).Name & "|"
End If
If x1 = 0 Then
sVals = sVals & rsSource.Fields(x).Value & "|"
Else
sVals = sVals & rsSource.Fields(x).Value & "|"
End If
Next x
If x1 = 0 Then
rsSource.MovePrevious
End If
Next x1

For x = 11 To 35
sFields = sFields & rsSource.Fields(x).Name & "|"
If rsSource.Fields(x).Value <> "" Then
sVals = sVals & CStr(rsSource.Fields(x).Value) & "|"
Else
sVals = sVals & "|"
End If
Next x
Print #LogFileNumber, sFields
Print #LogFileNumber, sVals
End If
End If
Close #LogFileNumber
Err:
MsgBox "The file is in use - please try again later."
End Function


It doesnt seem to work in this function (It excecute even if there is no error)

[si_the_geek]

You need to change the last few lines so that the message is only shown if there is actually an error. The label (Err: in this case) is just a marker, and doesn't have any effect on the code.

Here are two solutions:

Close #LogFileNumber
 
Exit Function
Err:
MsgBox "The file is in use - please try again later."
End Function

Close #LogFileNumber
Err:
If Err <> 0 Then
  MsgBox "The file is in use - please try again later."
End If
End Function