vba help - function is workbook open not working

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
938
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
 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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
Here's a working function to check if a workbook is open:

VBA Code:
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
 
Last edited:

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
938
Office Version
  1. 2010
Platform
  1. Windows
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

VBA Code:
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

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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.
 
Last edited:

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
938
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi Vbasics.

Below is the snapshot. taken from video.

1604261568299.png


Thanks
mg
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,502
Office Version
  1. 2013
Platform
  1. Windows
@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.

VBA Code:
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

Well-known Member
Joined
Feb 4, 2009
Messages
938
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi GWteb,

Perfect it worked ! Millions of thanks for your help ! 🕺 (y)



Thanks
mg
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
@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.

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,502
Office Version
  1. 2013
Platform
  1. Windows
Hi GWteb,

Perfect it worked ! Millions of thanks for your help ! 🕺 (y)

@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

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
@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.
 

Forum statistics

Threads
1,137,155
Messages
5,679,923
Members
419,862
Latest member
Bluewings666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top