vba help - function is workbook open not working

[Mallesh23]

Hi Team,

I am using below function , to check whether there is already Open workbook , but its not working.
Also what is the meaning of below lines not getting can you add comment it. or Any alternate code check open file.

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

Sub test()
Dim wbk2 As Workbook

Dim str As String
str = "E:\VBA\009 - Enter Time in Cell.xlsm"

Dim wbk As Workbook
Set wbk = OpenWorkbook(str, False, True)

If wbk Is Nothing Then

    GoTo myend

End If


myend:

On Error Resume Next
wbk.Close False
wbk2.Close False
Set wbk = Nothing
On Error GoTo 0

End Sub


Function OpenWorkbook(ByVal sFilename As String, ByVal updatelinks As Boolean, ByVal ReadOnly As Boolean) As Workbook

On Error GoTo eh

'Check file exists

    If (Dir(sFilename) <> "") Then
        'if workbook is already open inform the user
        If IsWorkBookOpen(sFilename) Then
            MsgBox ("The workbook[" + sFilename + " ] is already open by user [" + Application.UserName + "].Please close the file and run Again.!")
          GoTo Done
        End If
        Set OpenWorkbook = Workbooks.Open(sFilename, updatelinks, ReadOnly)
    Else
   
    MsgBox ("The workbook " & sFilename & " Could not be found.")
   
    End If
   
Done:

Exit Function


eh:

MsgBox Err.Description


End Function


Function IsWorkBookOpen(FileName As String)

    Dim fileID As Long
    Dim errNum As Long

    'Open file and Check for Error
   
    On Error Resume Next
    fileID = FreeFile()   ' from below how this function works can you add comment.
    Open FileName For Input Lock Read As #fileID
    Close fileID
        errNum = Err
    On Error GoTo 0
   
End Function

Thanks
mg

 

[VBasic2008]

Here's a working function to check if a workbook is open:

Function isWorkbookOpen(FileName As String) As Boolean
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(FileName)
    On Error GoTo 0
    isWorkbookOpen = Not wb Is Nothing
End Function

Sub TESTisWorkbookOpen()
    Debug.Print isWorkbookOpen("\\\")
    Debug.Print isWorkbookOpen("Test.xlsm")
End Sub

 

[Mallesh23]

Hi Vbasics2008,

Thanks for your help it worked.

One more query, from below question where are these two lines.

fileID = FreeFile()
Open FileName For Input Lock Read As #fileID

Function IsWorkBookOpen(FileName As String)

    Dim fileID As Long
    Dim errNum As Long

    'Open file and Check for Error
   
    On Error Resume Next
    fileID = FreeFile()   
    Open FileName For Input Lock Read As #fileID
    Close fileID
        errNum = Err
    On Error GoTo 0
   
End Function

Thanks
mg

 

[VBasic2008]

Hi Vbasics2008,

Thanks for your help it worked.

One more query, from below question where are these two lines.

fileID = FreeFile()
Open FileName For Input Lock Read As #fileID

Function IsWorkBookOpen(FileName As String)

    Dim fileID As Long
    Dim errNum As Long

    'Open file and Check for Error
 
    On Error Resume Next
    fileID = FreeFile() 
    Open FileName For Input Lock Read As #fileID
    Close fileID
        errNum = Err
    On Error GoTo 0
 
End Function

Thanks
mg

This is part of a code that is usually used to open a text file and read or write to it. I don't know, maybe someone got a solution using this code, I cannot figure it out. Something is definitely missing, because the function should return True or False, which it cannot.

 

[Mallesh23]

Hi Vbasics.

Below is the snapshot. taken from video.

Thanks
mg

 

[GWteB]

@Mallesh23, your post #3 code is apparently a snippet of a larger procedure, and not a function anyway considering how the code is written.
Unfortunately, @VBasic2008's code doesn't work reliably either.
There are several ways to check whether a workbook is already open. Below an example, derived from the code from post # 3.
I've added some remarks.

Sub Example()

    Dim bBool As Boolean
    bBool = isWorkbookOpen("C:\Users\Folder\Book.xlsm")

End Sub


Function isWorkbookOpen(FileName As String) As Boolean

    Dim fileID As Long
    Dim errNum As Long

    ' retrieve file handle from Windows OS
    fileID = FreeFile()
    
    ' don't act on errors for now
    On Error Resume Next
    
    ' open file for reading en try to LOCK (!!) this file
    ' to prevent other processes / programs accessing this file
    Open FileName For Input Lock Read As #fileID
    
    ' if LOCKING fails, an error occurs
    ' assign error number to variabele
    errNum = Err.Number
    
    ' If lock succeeded, file has to be closed, using its file handle
    Close fileID
    
    ' if error was 0, there where no errors, ....
    ' ... so opening and locking file succeeded, so it was not open before
    ' in VBA the number 0 equals FALSE, any other number equals TRUE
    ' convert error number to boolean and return result
    isWorkbookOpen = CBool(errNum)

End Function

 

[Mallesh23]

Hi GWteb,

Perfect it worked ! Millions of thanks for your help ! ?



Thanks
mg

 

[VBasic2008]

@Mallesh23, your post #3 code is apparently a snippet of a larger procedure, and not a function anyway considering how the code is written.
Unfortunately, @VBasic2008's code doesn't work reliably either.
There are several ways to check whether a workbook is already open. Below an example, derived from the code from post # 3.
I've added some remarks.

Sub Example()

    Dim bBool As Boolean
    bBool = isWorkbookOpen("C:\Users\Folder\Book.xlsm")

End Sub


Function isWorkbookOpen(FileName As String) As Boolean

    Dim fileID As Long
    Dim errNum As Long

    ' retrieve file handle from Windows OS
    fileID = FreeFile()
  
    ' don't act on errors for now
    On Error Resume Next
  
    ' open file for reading en try to LOCK (!!) this file
    ' to prevent other processes / programs accessing this file
    Open FileName For Input Lock Read As #fileID
  
    ' if LOCKING fails, an error occurs
    ' assign error number to variabele
    errNum = Err.Number
  
    ' If lock succeeded, file has to be closed, using its file handle
    Close fileID
  
    ' if error was 0, there where no errors, ....
    ' ... so opening and locking file succeeded, so it was not open before
    ' in VBA the number 0 equals FALSE, any other number equals TRUE
    ' convert error number to boolean and return result
    isWorkbookOpen = CBool(errNum)

End Function

I obviously never thought about it this way. Will have to investigate further. About the code I posted, since you said it is unreliable, could you describe a scenario when it would fail? And maybe post a link to a better solution?

 

[GWteB]

Hi GWteb,

Perfect it worked ! Millions of thanks for your help ! ?

@Mallesh23, you are welcome and thanks for letting me know.

@VBasic2008,
In your code you are looking within the Workbooks collection by index in order to obtain a Workbook object.
The idea that if this fails (giving you a Nothing) is correct, however ..... the following has to be taken into account:
1. the first, but least common situation is that when a Workbook is opened in Protected View, it's not part of the Workbooks collection;
2. of more importance is the fact that the Workbooks collection contains only the "bare" file name, without a path.
The latter makes a file on disk (the workbook) unique.

Suppose the workbook F:\SomeFolder\book.xls is currently open and on that very same moment one wants to determine whether the workbook C:\Users\Folder\BOOK.XLS is open. The Workbooks collection contains a "book.xls" (the one on the F: drive) so a valid Workbook object is obtained (with respect to the case insensitivity of the Windows OS's file system).
Your code incorrectly returns a TRUE because the check concerned a workbook with a different path (on the C: drive).

 

[VBasic2008]

@Mallesh23, you are welcome and thanks for letting me know.

@VBasic2008,
In your code you are looking within the Workbooks collection by index in order to obtain a Workbook object.
The idea that if this fails (giving you a Nothing) is correct, however ..... the following has to be taken into account:
1. the first, but least common situation is that when a Workbook is opened in Protected View, it's not part of the Workbooks collection;
2. of more importance is the fact that the Workbooks collection contains only the "bare" file name, without a path.
The latter makes a file on disk (the workbook) unique.

Suppose the workbook F:\SomeFolder\book.xls is currently open and on that very same moment one wants to determine whether the workbook C:\Users\Folder\BOOK.XLS is open. The Workbooks collection contains a "book.xls" (the one on the F: drive) so a valid Workbook object is obtained (with respect to the case insensitivity of the Windows OS's file system).
Your code incorrectly returns a TRUE because the check concerned a workbook with a different path (on the C: drive).

Thanks for the insight. Will have to do some investigating.