checking file already open status

[John F. Collins]

Another user has a tab delimited text file named myFile.xls open on a
server, writing to the file with Labview, and periodically flushing to
disk. While he still has the file open, I open the same file in excel using
workbooks.open, with ReadOnly:= true. The Labview user tells me this
freezes the file so that he can no longer write to it.

How can I avoid having Excel open for exclusive use, even though it is read
only, and already open by someone else?

If I can't do that, then how can I check the status of the file to make sure
that no one has it open before I try to open it?

John

 

[Leith Ross]

Hello John,

Here is a macro from Microsoft that checks if a file is already open
It returns true if the file is open, false otherwise. Insert a VB
module into your workbook and paste this code into it.

EXAMPLE
Test = IsFileOpen("C:\My Documents\MyFile.xls")


Code
-------------------
' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error occurs because there is
' some other problem accessing the file.

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error errnum
End Select

End Function

 

[Gary Keramidas]

here's one from bob phillips

Dim oWB As Workbook

On Error Resume Next
Set oWB = Workbooks("myBook")
On Error GoTo 0
If oWB Is Nothing Then
Set oWB = Workbooks.Open("C:\myDir\myBook.xls")
End If
oWB.Worksheets("Sheet1").Activate