I have a "Master Workbook" that (with VBA) moves worksheets out to other
workbooks located in subfolders in the same shared drive. My problem is that
I can't move a worksheet if another network user has that particular workbook
in the subfolder open (and nothing tells me that it didn't move.) How can I
check to see if someone (not me) has a workbook open and if so skip the move.
I found a UDF that someone posted that isn't working for me and I'm thinking
that's because I'm not the one who has the subfolder workbook open. Here is
the function that's not working:

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' This function checks to see if someone has a template file open
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

and here is some of my Macro that tries to use that function:

SavePath = ActiveWorkbook.Path
....
....
template_place = "MBE SBR"
Sheets("MBE SBR").Select

If bIsBookOpen(SavePath & "\PROFILE (DFA and OS)\MBE SBR TEMPLATE.xls")
Then GoTo open_template

Workbooks.Open Filename:= _
SavePath & "\PROFILE (DFA and OS)\MBE SBR TEMPLATE.xls"
....
....
Sheets("MBE SBR").Move Befo=Workbooks("MBE SBR TEMPLATE.xls"). _
Sheets(1)
....
....
PROFILE_template:
....'the next move of another sheet
....
open_template:
MsgBox Prompt:="That template file is open so I can't distribute that
template."
nxt_template:
If template_place = "MBE SBR" Then GoTo PROFILE_template

Can anyone tell me what's wrong with my code or suggest alternative code to
use to do what I want to do?

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Sub test()
If Not IsFileOpen("C:\MyTest\volker2.xls") Then
Workbooks.Open "C:\MyTest\volker2.xls"
End If
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jim K." wrote in message
...
I have a "Master Workbook" that (with VBA) moves worksheets out to other
workbooks located in subfolders in the same shared drive. My problem is
that
I can't move a worksheet if another network user has that particular
workbook
in the subfolder open (and nothing tells me that it didn't move.) How can
I
check to see if someone (not me) has a workbook open and if so skip the
move.
I found a UDF that someone posted that isn't working for me and I'm
thinking
that's because I'm not the one who has the subfolder workbook open. Here
is
the function that's not working:

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' This function checks to see if someone has a template file open
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

and here is some of my Macro that tries to use that function:

SavePath = ActiveWorkbook.Path
...
...
template_place = "MBE SBR"
Sheets("MBE SBR").Select

If bIsBookOpen(SavePath & "\PROFILE (DFA and OS)\MBE SBR TEMPLATE.xls")
Then GoTo open_template

Workbooks.Open Filename:= _
SavePath & "\PROFILE (DFA and OS)\MBE SBR TEMPLATE.xls"
...
...
Sheets("MBE SBR").Move Befo=Workbooks("MBE SBR TEMPLATE.xls"). _
Sheets(1)
...
...
PROFILE_template:
...'the next move of another sheet
...
open_template:
MsgBox Prompt:="That template file is open so I can't distribute that
template."
nxt_template:
If template_place = "MBE SBR" Then GoTo PROFILE_template

Can anyone tell me what's wrong with my code or suggest alternative code
to
use to do what I want to do?

Thanks.

"Bob Phillips" wrote:


Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Sub test()
If Not IsFileOpen("C:\MyTest\volker2.xls") Then
Workbooks.Open "C:\MyTest\volker2.xls"
End If
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jim K." wrote in message
...
I have a "Master Workbook" that (with VBA) moves worksheets out to other
workbooks located in subfolders in the same shared drive. My problem is
that
I can't move a worksheet if another network user has that particular
workbook
in the subfolder open (and nothing tells me that it didn't move.) How can
I
check to see if someone (not me) has a workbook open and if so skip the
move.
I found a UDF that someone posted that isn't working for me and I'm
thinking
that's because I'm not the one who has the subfolder workbook open. Here
is
the function that's not working:

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' This function checks to see if someone has a template file open
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

and here is some of my Macro that tries to use that function:

SavePath = ActiveWorkbook.Path
...
...
template_place = "MBE SBR"
Sheets("MBE SBR").Select

If bIsBookOpen(SavePath & "\PROFILE (DFA and OS)\MBE SBR TEMPLATE.xls")
Then GoTo open_template

Workbooks.Open Filename:= _
SavePath & "\PROFILE (DFA and OS)\MBE SBR TEMPLATE.xls"
...
...
Sheets("MBE SBR").Move Befo=Workbooks("MBE SBR TEMPLATE.xls"). _
Sheets(1)
...
...
PROFILE_template:
...'the next move of another sheet
...
open_template:
MsgBox Prompt:="That template file is open so I can't distribute that
template."
nxt_template:
If template_place = "MBE SBR" Then GoTo PROFILE_template

Can anyone tell me what's wrong with my code or suggest alternative code
to
use to do what I want to do?