» Determine if a workbook is already open using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions The function below can be used to determine if a workbook is open or not:

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.WorkBooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

Example:

If Not WorkbookOpen("MyWorkbookName.xls") Then 
    Workbooks.Open "MyWorkbookName.xls"
End If