| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Open Workbook using URL instead of network path | Excel Programming | |||
| testing for open workbook | Excel Programming | |||
| Is workbook open anywhere in network? | Excel Programming | |||
| Is workbook open on the network? | Excel Programming | |||
| Testing for open workbook | Excel Programming | |||
Linear Mode
