Check if a workbook is open on a network.

[Spreadsheet Solutions]

Dear all;

I need to check if someone on the network is using a workbook called "BRS".
How do I do that ?

I have this function (by Andy Pope), but can't get it to work.
'-----------------------------------------------------------------
Function IsNetworkFileOpen(Filename As String)
'-----------------------------------------------------------------
Dim nFile As Long

IsNetworkFileOpen = False

nFile = FreeFile()
On Error Resume Next
Open Filename For Input Lock Read Write As #nFile
If Err <> 0 Then
If Err.Number = 70 Then
IsNetworkFileOpen = True
Else
IsNetworkFileOpen = "No such file"
End If
End If
On Error GoTo 0
Close #nFile

End Function
'-----------------------------------------------------------------

Were does Workbooks("BRS.xls") comes into play ?


Many thanks beforehand.

 

[Alan]

Sub TestFileOpened()

' Test to see if the file is open.
If IsFileOpen("C:\Book2.xls") Then 'Change this to
network path: \\F:\Folder1\Folder2
' Display a message stating the file in use.
MsgBox "File already in use!"
'
' Add code here to handle case where file is open by another
' user.
'
Else
' Display a message stating the file is not in use.
MsgBox "File not in use!"
' Open the file in Microsoft Excel.
Workbooks.Open "C:\Book2.xls" 'Change this to
network path: \\F:\Folder1\Folder2

'
' Add code here to handle case where file is NOT open by another
' user.
'
End If


Copied from Microsoft's website, except for my two network remarks.

Regards,

Alan

 

[Chip Pearson]

You call that function from another procedure with code such as

Dim IsOpen As Boolean
IsOpen = IsNetworkFileOpen(Workbooks("BRS.xls").FullName)
If IsOpen = True Then
' do something - file is open by another process or user
Else
' do something else - file is not open
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Spreadsheet Solutions]

Chip;

Thanks for the feedback.
I overlooked the reference to the correct path.
It works fine now !!