Detect whether Excel workbook is already open [closed]

Question

In VBA, I opened an MS Excel file named "myWork.XL" programmatically.

Now I would like a code that can tell me about its status - whether it is open or not. I.e. something like IsWorkBookOpened("myWork.XL) ?

Answer 1

For my applications, I generally want to work with a workbook rather than just determine if it's open. For that case, I prefer to skip the Boolean function and just return the workbook.

Sub test()      Dim wb As Workbook      Set wb = GetWorkbook("C:\Users\dick\Dropbox\Excel\Hoops.xls")      If Not wb Is Nothing Then         Debug.Print wb.Name     End If  End Sub  Public Function GetWorkbook(ByVal sFullName As String) As Workbook      Dim sFile As String     Dim wbReturn As Workbook      sFile = Dir(sFullName)      On Error Resume Next         Set wbReturn = Workbooks(sFile)          If wbReturn Is Nothing Then             Set wbReturn = Workbooks.Open(sFullName)         End If     On Error GoTo 0      Set GetWorkbook = wbReturn  End Function 

Answer 2

Try this:

Option Explicit  Sub Sample()     Dim Ret      Ret = IsWorkBookOpen("C:\myWork.xlsx")      If Ret = True Then         MsgBox "File is open"     Else         MsgBox "File is Closed"     End If End Sub  Function IsWorkBookOpen(FileName As String)     Dim ff As Long, ErrNo As Long      On Error Resume Next     ff = FreeFile()     Open FileName For Input Lock Read As #ff     Close ff     ErrNo = Err     On Error GoTo 0      Select Case ErrNo     Case 0:    IsWorkBookOpen = False     Case 70:   IsWorkBookOpen = True     Case Else: Error ErrNo     End Select End Function