Function to show a file is open

kkjensen

Active Member
Joined
Jun 22, 2004
Messages
352
Is there a function that can tell if a file is open? I'm trying to dummy proof a workbook and I have some files opening automatically as read-only and being hidden. I just want to have some sort of visible indicator in a cell that the file is already currently open.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Code:
Private Function WorkbookIsOpen(wbname) As Boolean
'   Returns TRUE if the workbook is open
    Dim x As Workbook
    On Error Resume Next
    Set x = Workbooks(wbname)
    If Err = 0 Then WorkbookIsOpen = True _
        Else WorkbookIsOpen = False
End Function

To use it, you have to use the file name--not the full path. For example:
Code:
If WorkbookIsOpen("File1.xls") = True then...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,386
Messages
5,571,836
Members
412,420
Latest member
grace_abar
Top