ExcelBanter - Testing if a workbook on the network is open

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Testing if a workbook on the network is open (https://www.excelbanter.com/excel-programming/375969-testing-if-workbook-network-open.html)

Testing if a workbook on the network is open

Excel 2000

I used the test open workbook code provided by Ron de Bruin on an earlier
post

Sub test()
If bIsBookOpen("test.xls") Then
MsgBox "Open"
Else
MsgBox "Not Open"
End If
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

I found this only works if the excel documentB being tested is in the same
folder as the macro documentA. If documentB is open and in a different
folder the function will return a not open variable.
Is it possible to change this code to test document B located at a different
network folder to see if the document is open?
I tried to replace "test.xls" with "networkDrive:\network folder\test.xls"
and it did not work.

Thanks

PJ

Testing if a workbook on the network is open

'/======================================/
Function IsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey modified by Gary Brown
'
Dim blnResult As Boolean, blnPath As Boolean
Dim strFileName As String, strPathName As String
Application.Volatile

On Error Resume Next

'step 1 - check to see if a path has been given by looking
' for a '\'
If Len(szBookName) - _
Len(Application.WorksheetFunction.Substitute(szBoo kName, _
"\", "")) < 0 Then
blnPath = True
End If

'step 2 - separate the filename from the path
If blnPath = True Then
strFileName = _
Right(szBookName, Len(szBookName) - _
Application.WorksheetFunction.Find("~", _
Application.WorksheetFunction.Substitute( _
szBookName, "\", "~", _
Len(szBookName) - _
Len(Application.WorksheetFunction.Substitute( _
szBookName, "\", "")))))

strPathName = _
Left(szBookName, _
Application.WorksheetFunction.Find("~", _
Application.WorksheetFunction.Substitute( _
szBookName, "\", "~", _
Len(szBookName) - _
Len(Application.WorksheetFunction.Substitute( _
szBookName, "\", "")))) - 1)
Else
strFileName = szBookName
strPathName = ""
End If

'step 3 - check that something was found
If Len(strFileName) < 0 Then
'step 4 - check that file is open
blnResult = _
Not (Application.Workbooks(strFileName) Is Nothing)
'step 5 - if open, verify that it is the file in the
' path you are analysing and not simply a
' file with the same name but from a
' different path
If blnResult = True Then
'if the files don't have the same path, they
' aren't the same BUT if a path wasn't given
' in the original variable, don't test for it
If Application.Workbooks(strFileName).Path < _
strPathName And blnPath = True Then
blnResult = False
End If
End If
End If

'report the results - True or False
IsBookOpen = blnResult

End Function
'/======================================/

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.

"PJ Usher" wrote:

Excel 2000

I used the test open workbook code provided by Ron de Bruin on an earlier
post

Sub test()
If bIsBookOpen("test.xls") Then
MsgBox "Open"
Else
MsgBox "Not Open"
End If
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

I found this only works if the excel documentB being tested is in the same
folder as the macro documentA. If documentB is open and in a different
folder the function will return a not open variable.
Is it possible to change this code to test document B located at a different
network folder to see if the document is open?
I tried to replace "test.xls" with "networkDrive:\network folder\test.xls"
and it did not work.

Thanks

PJ

Testing if a workbook on the network is open

Thank you for your response. Unfortunately it does not work.
I have the spreadsheet that it is looking at open and it gives me a message
that it is closed.

I think it breaks at

'step 4 - check that file is open
blnResult = _
Not (Application.Workbooks(strFileName) Is Nothing)

Always ends up with blnresult = false

This is what I see when I mouse over specific parts of the code:
application.workbooks(strFileName) = <subscript out of range
strFileName = DocumentB
Nothing = Nothing.

Thank you again for your help

PJ

"Gary Brown" wrote in
message ...
'/======================================/
Function IsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey modified by Gary Brown
'
Dim blnResult As Boolean, blnPath As Boolean
Dim strFileName As String, strPathName As String
Application.Volatile

On Error Resume Next

'step 1 - check to see if a path has been given by looking
' for a '\'
If Len(szBookName) - _
Len(Application.WorksheetFunction.Substitute(szBoo kName, _
"\", "")) < 0 Then
blnPath = True
End If

'step 2 - separate the filename from the path
If blnPath = True Then
strFileName = _
Right(szBookName, Len(szBookName) - _
Application.WorksheetFunction.Find("~", _
Application.WorksheetFunction.Substitute( _
szBookName, "\", "~", _
Len(szBookName) - _
Len(Application.WorksheetFunction.Substitute( _
szBookName, "\", "")))))

strPathName = _
Left(szBookName, _
Application.WorksheetFunction.Find("~", _
Application.WorksheetFunction.Substitute( _
szBookName, "\", "~", _
Len(szBookName) - _
Len(Application.WorksheetFunction.Substitute( _
szBookName, "\", "")))) - 1)
Else
strFileName = szBookName
strPathName = ""
End If

'step 3 - check that something was found
If Len(strFileName) < 0 Then
'step 4 - check that file is open
blnResult = _
Not (Application.Workbooks(strFileName) Is Nothing)
'step 5 - if open, verify that it is the file in the
' path you are analysing and not simply a
' file with the same name but from a
' different path
If blnResult = True Then
'if the files don't have the same path, they
' aren't the same BUT if a path wasn't given
' in the original variable, don't test for it
If Application.Workbooks(strFileName).Path < _
strPathName And blnPath = True Then
blnResult = False
End If
End If
End If

'report the results - True or False
IsBookOpen = blnResult

End Function
'/======================================/

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of
the
post.

"PJ Usher" wrote:

Excel 2000

I used the test open workbook code provided by Ron de Bruin on an
earlier
post

Sub test()
If bIsBookOpen("test.xls") Then
MsgBox "Open"
Else
MsgBox "Not Open"
End If
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

I found this only works if the excel documentB being tested is in the
same
folder as the macro documentA. If documentB is open and in a different
folder the function will return a not open variable.
Is it possible to change this code to test document B located at a
different
network folder to see if the document is open?
I tried to replace "test.xls" with "networkDrive:\network
folder\test.xls"
and it did not work.

Thanks

PJ

Testing if a workbook on the network is open