What is the test for whether a file is open?

[Jim Luedke]

What's the test for whether a file is currently open (or closed)?

My app writes to a log file, but I don't want to burden the caller
with opening the file in the event he never has to write to it. So I
want:

Dim H As Long

Sub Log(Msg as String)
If Not IsOpen(H) then
Open LogFile For Output As #H
Print #H, FileHeader & DateTime
Print #H,
End If

Print #H, Msg
End Sub

Sub Main
Repeat
If Not Something Then
Log "Oops"
End If
Until SomethingElse
Close #H
End Sub

So what expression should "IsOpen(H)" be?

Thanks.

***

 

[Mike H]

Jim,

So what expression should "IsOpen(H)" be?

There isn't one you have to do it with code. heres a function you can caal
from your code and ive included a sample sub to text it. basically the
function opens and close the file and sees if an error occurs. No error, file
isn't open, error 70, file open.


Sub TestFileOpened()
If IsOpen("c:\Book2.xls") Then
MsgBox "File Open"
Else
MsgBox "File not open"
End If
End Sub

Function IsOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0
Select Case errnum
' NOT open.
Case 0
IsFileOpen = False
' File open
Case 70
IsFileOpen = True
End Select
End Function

Mike

 

[Mike H]

Jim,

Sorry the previous version has a typo, use this instead

Sub TestFileOpened()
If IsOpen("c:\Book2.xls") Then
MsgBox "File Open"
Else
MsgBox "File not open"
End If
End Sub

Function IsOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0
Select Case errnum
' NOT open.
Case 0
IsOpen = False
' File open
Case 70
IsOpen = True
End Select
End Function

Mike

 

[Peter T]

"I don't want to burden the caller
with opening the file in the event he never has to write to it."

What does anything you do "burden" the user, why would he ever/never want to
write to it.

Determining if a text-file is open, while possible, is not always
straightforward. It depends which app has opened the file. Typically with
most text editors, even if the file is open you can 'Output' or 'Append' the
file. Notepad will not update though some others will refresh with latest
contents when reactivated. However if the file is open in say Word the code
will fail with permission denied, so you can trap for that.

In passing, wouldn't you normally want to Append the log rather than rewrite
it entirely each time.

Regards,
Peter T

 

[Jim Luedke]

Thanks both for reply.

***

Peter:

My VBA function is the only entity that ever opens the file. So my
question is, and I probably should've been more explicit:

What is the test for whether a file, *which only you yourself ever
open, inside VBA*, is currently open?

***

Mike:

Thanks very much for code. I'll give it a try.

But, are you saying that VBA has no expression telling you whether a
file *it itself* opened, is open or not? That seems odd to me.

Somewhere along the line I got the impression that you could, maybe,
test the file handle or something. If H is the handle, does something
like this work?

If H = 0 then
IKnowThatFileIsClosed = True
End If

On the other hand, when I put a trace on:

Close #H

it seems that the value of H doesn't change, i.e. become 0, on
closing. I thought it did. So I must be all wet.

Thanks much again.

***

 

[Peter T]

Are you talking about a workbook file (as Mike understood) or a text file
(as I understood). If the latter are you talking about opening the textfile
in Excel, some text editor or with the Open filenumber statement

Regards,
Peter T