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.
--
--
Mark Rosenkrantz
--

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


"Spreadsheet Solutions" <(E-Mail Removed)> wrote in message
news:45edad6a$0$380$(E-Mail Removed)...
> 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.
> --
> --
> Mark Rosenkrantz
> --
>
>

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" <(E-Mail Removed)> wrote in message
news:45edad6a$0$380$(E-Mail Removed)...
> 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.
> --
> --
> Mark Rosenkrantz
> --
>
>

Chip;

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

--
--
Mark Rosenkrantz
--
Spreadsheet Solutions
Witkopeend 24
1423 SN, Uithoorn
Netherlands
--
W: www.rosenkrantz.nl
E: (E-Mail Removed)
--
"Chip Pearson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:45edad6a$0$380$(E-Mail Removed)...
>> 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.
>> --
>> --
>> Mark Rosenkrantz
>> --
>>
>>
>
>